Excel Macro - Repeat a process

Question

I have a recorded macro, for a simple process in Excel. However, I need it to repeat the process for about 80 lines. Here is the code I have for the first 4 lines. Any help on a simple way to do this would be appreciated. Thank you.

Sub Macro2()
'
' Macro2 Macro
'

'
Range("A5").Select
ActiveCell.FormulaR1C1 = "1"
Sheets("EST COST").Select
Range("D6").Select
Selection.Copy
Sheets("IL").Select
Range("I5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Range("A5").Select
ActiveCell.FormulaR1C1 = "0"

Range("A6").Select
ActiveCell.FormulaR1C1 = "1"
Sheets("EST COST").Select
Range("D6").Select
Selection.Copy
Sheets("IL").Select
Range("I6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Range("A6").Select
ActiveCell.FormulaR1C1 = "0"

Range("A7").Select
ActiveCell.FormulaR1C1 = "1"
Sheets("EST COST").Select
Range("D6").Select
Selection.Copy
Sheets("IL").Select
Range("I7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Range("A7").Select
ActiveCell.FormulaR1C1 = "0"
End Sub

Read through [how to avoid using `.Select`\`.Activate`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros), this will **greatly** cut down the code, and shows you how to work directly with your data. — BruceWayne, Apr 14 '16 at 14:38

score 2 · Answer 1 · answered Apr 14 '16 at 14:36

You want to use a for...next loop. Some Googling should get you quite far, but here's a flavour of the general idea:

dim startRow as integer
dim endRow as integer
dim myColumn as integer

startRow = 5
endRow = 45
For activeRow = startRow to endRow
    [do something]
    myColumn = [some column number]
    cells(activeRow, myColumn).Value = [something]
Next activeRow

score 0 · Answer 2 · answered Apr 14 '16 at 14:39

Something like this

Sub test()

    Dim wsTarget As Worksheet
    Dim wsSource As Worksheet

    Set wsTarget = Sheets("EST COST")
    Set wsSource = Sheets("IL")

    Dim intIndex As Integer
    For intIndex = 5 To 85

        wsTarget.Range("A" & intIndex).FormulaR1C1 = "1"
        wsTarget.Range("D" & intIndex).Copy
        With wsSource
            .Range("I" & intIndex).PasteSpecial Paste:=xlPasteValues _
            , Operation:=xlNone, SkipBlanks:=False, Transpose:=False

            .Range("A" & intIndex).FormulaR1C1 = "0"
        End With
    Next

End Sub

score 0 · Answer 3 · answered Apr 14 '16 at 14:58

To keep your code as similar as you have it, try this:

Sub test()
Dim rng As Range
Dim i&

For i = 5 To 40
' WHAT SHEET IS YOUR DEFAULT RANGES ON?
Range("A" & i).FormulaR1C1 = "1" ' what sheet is this on? We want to be explicit
Sheets("EST COST").Range("D" & i + 1).Copy
Sheets("IL").Range("I" & i).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Range("A" & i).FormulaR1C1 = "0"
Next i

End Sub

I'm assuming you want the pasted range to be offset one row (you copy A5, pasted into I6). As I noted though, I'd prefer to know what sheet your ranges to be copied are on, so we can add that worksheet to the ranges (Range("A"& i)... should really be Sheets("mainSheet").Range("A"&i)...)

Thank you very much. This worked perfectly. I only had to change the range for the copied cell. That was always going to be "D6". — Ryan Ames, Apr 14 '16 at 16:34
@RyanAmes - If it worked for you, do you mind marking as the answer? Also, see how to get rid of `.Select`? basically, you can "back up" two lines, one that ends with `.Select` and the next starting with `Selection.` to work directly with the data. — BruceWayne, Apr 15 '16 at 13:44

Excel Macro - Repeat a process

3 Answers3