CODEWITHSUNDEEP
vbaexcelmacros
Ryan Ames
Ryan Ames

Reputation: 1

Excel Macro - Repeat a process

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

Upvotes: 0

Views: 308

Answers (3)

BruceWayne
BruceWayne

Reputation: 23283

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)...)

Upvotes: 0

user6028892
user6028892

Reputation:

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

Upvotes: 0

NiH
NiH

Reputation: 466

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

Upvotes: 2

Related Questions