Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2004
    Posts
    1

    Unhappy Unanswered: Need help w/repeating a macro in a different location.

    Please help! I'm trying to repeat a macro in different locations on the same excel sheet or trying to use it on the entire sheet if possible. Can't figure out how to keep it from just performing the macro on the cells it was recorded on. I'd like to be able to start it anywhere and have it perform the exact process and values. Thanks and here it is:
    Sub DataUpdate()
    '
    ' DataUpdate Macro
    ' Macro recorded 10/3/2004 by Steve N Keiser
    '
    ' Keyboard Shortcut: Ctrl+d
    '
    Range("D3:N3").Select
    Selection.Copy
    Range("D2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("D6:N6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("D5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("D4").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "0"
    Range("E4").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("F4").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G4").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("H4").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("I4").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("J4").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("K4").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("L4").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("M4").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("N4").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("D7").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("E7").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("F7").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G7").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("H7").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("I7").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("J7").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("K7").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("L7").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("M7").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("N7").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("O7").Select
    End Sub

  2. #2
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    Welcome to the Board!

    First, this code will speed up your process on your original ranges:
    Code:
    Sub DataUpdate()
        
        With Application
            .ScreenUpdating = False
            
                Range("D3:N3").Copy
                
                With Range("D2")
                    .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
                End With
                
                Range("D6:N6").Copy
                
                With Range("D5")
                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
                End With
                
                Range("D4:N4, D7:O7") = "0"
            
            .CutCopyMode = False
            .ScreenUpdating = True
        End With
        
    End Sub
    For the random copy, what criteria do you want for the copy to take place? Input box or select a range then invoke the code?

    Post back and someone can modify the code to accomodate your needs depending on how you want it.

    Hope that helps,

    Smitty

  3. #3
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas, USA
    Posts
    68
    Here's one way you could go about it:
    Code:
    Sub test()
    With Selection
        .Rows(1).Value = .Rows(2).Value
        .Rows(3).Value = "0"
        .Rows(4).Value = .Rows(5).Value
        .Rows(6).Value = "0"
    End With
    End Sub
    With this macro, if you preselect a range of cells that include at least seven rows, it will copy the contents of all cells in the second row to the first row, set all values in the third row to zero, etc.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •