Need help w/repeating a macro in a different location.

CaliDolfan1313 · #1 (**permalink**) 10-03-04, 13:21

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

Smitty · #2 (**permalink**) 10-03-04, 15:37

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

actuary · #3 (**permalink**) 10-10-04, 20:05

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.