1

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
Improve this question
3
  • 1
    for each cell in range Commented Mar 18, 2016 at 15:41
  • So, you need to copy each cell in column H, and place in "Weekly Productivity" sheet? I assume in the next available place? What are you trying to accomplish? If you just need to do all cells in H, why not just copy the whole column (Range("H:H").Copy)? Commented Mar 18, 2016 at 15:41
  • Sorry for the confusion... "Weekly Productivity" is a report to be individually generated for each Specialist. Whenever an employee ID is entered into the input cell, that person's details is populated. Currently, in order to generate all the pdfs, the IDs have to be entered manually or selected from a drop list. Column H in "Specialist Roster" lists all active Specialist's ID number. I am trying to automate the process of generating PDFs for everyone by way of telling the code to "go through this list and create a PDF for every ID in this list". Commented Mar 18, 2016 at 15:46

3 Answers 3

Reset to default
3

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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

IMO, this is the better answer for efficiency and ease of maintaining.
3

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
Improve this answer

7 Comments

I don't need to paste the entire column H into the weekly report tab. I need the code to 1. Grab the first ID (H2); 2. Paste that ID number into the weekly tab (H1); 3. Save as PDF; 4) Go back to the roster and grab the next ID number (H3) and paste it into the weekly tab (H2); 5. Repeat this same task for all the IDs listed in column H of the roster.
ArcherBird's example worked perfectly, but the code stops after a few PDF have already been generated for some reason.
I think that is more PDF-generating related issue, and deserves a new question in my opinion. You should there specify the library you use and provide some of the PDF generating code you have. It is quite a different question.
I added a loop as alternative. Maybe try that. As I don't know the PDF side, the source of your problems might still to be found there and not in your loop.
The pdf-generating aspect works perfectly and is not the issue. Thank you for your help.
|
1

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

Improve this answer

7 Comments

I know, I just copy and pasted the OP out of laziness
if you clean up the .Select, I'll take away the dv.
you got yourself a deal!
Still have one, the second and third lines should be one Sheets("Specialist Roster").Range("H" & i).Copy
You can also edit it so you don't have to select Sheets("Specialist Roster") how about Sheets("Specialist Roster").Range("H" & i).Copy or use with sh.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.