CODEWITHSUNDEEP
vbaexcelloopsfor-loop
Sim1
Sim1

Reputation: 13

How to repeat VBA code using For-Loop

I need help executing the first code for every ID in column H. I've tried establishing a "loop" and a "for next" to no avail.

I'd like to establish a type of "i = row number" and have a "i + 1" command that will perform the same task for every cell in the column

Any help is sincerely appreciated!

Sub GenerateAll_1()

'Copy the first ID in the list (cell H2) and paste it
    Sheets("Specialist Roster").Select
    Range("H2").Select
    Selection.Copy
    Sheets("Weekly Productivity").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'My code that saves as pdf based on other criteria goes here

'REPEAT task for the cells H3, H4... H260

Upvotes: 1

Views: 1879

Answers (3)

trincot
trincot

Reputation: 350272

To only copy the data, as your code suggests (xlPasteValues), do like this:

Sheets("Weekly Productivity").Range("H2:H260") = 
    Sheets("Specialist Roster").Range("H2:H260").Value

Without loop.

You can even do it without code, and just put a formula in "Weekly Productivity":

In cell H2:

='Specialist Roster'!H2

... and drag down as far as needed.

If you really need the loop (because of other tasks you want to do based on intermediate results), then go for this:

Sub GenerateAll_1()
    Dim source As Range ' cell from which you copy
    Dim target As Range ' cell to which you copy

    Set target = Sheets("Weekly Productivity").Range("H1")

    For Each source In Sheets("Specialist Roster").Range("H2:H260")
        target = source.Value ' copy the value (only)
        Set target = target.Offset(1) ' let target point to next cell
        ' perform other tasks here
    Next

End Sub

Upvotes: 3

ArcherBird
ArcherBird

Reputation: 2134

This example is really just to give you an idea of how a for loop would work here. It should be noted that looping is NOT the most efficient way of copying and pasting values between sheets. I think some of the other solutions better address the efficient ways:

For i = 2 To 260
Sheets("Specialist Roster").Range("H" & i).Copy
'here you need to specify what range you want to copy to
Sheets("Weekly Productivity").Range("H" & i).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
 Next i

this is not a Great solution for scaling because it is hard coded to do rows 2 through 260. But it can be made better using some variables. Hope this is somewhat helpful...

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

Here is a one line sub:

Sub GenerateAll_1()
    Sheets("Specialist Roster").Range("H2:H" & Rows.Count).Copy Sheets("Weekly Productivity").Range("H2")
End Sub

No loops are required. If you want to avoid the values in column H below row 260, then:

Sub GenerateAll_1()
    Dim r1 As Range, r2 As Range

    Set r1 = Sheets("Specialist Roster").Range("H2:H260")
    Set r2 = Sheets("Weekly Productivity").Range("H2")

    r1.Copy r2
End Sub

Upvotes: 3

Related Questions