Help with For-Next loop

[Jeff Meyers]

Using Excel 2010 on a Windows 7 PC<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am creating numerous Excel files for employee timesheets. Hours are tracked by day by program grant for bi-weekly pay periods. Each employee will have a separate Excel file containing 27 worksheets (1 master and 26 - one for each pay period). I will create the “Master” to properly allocate each employee’s time to the various program grants. I then need to copy the hours information from the “Master” to each of the pay period worksheets. I named ranges (both on the Master and on each of the pay period sheets) for this process.<o></o>
<o></o>
I created (recorded) a macro to accomplish this, but it is inefficient and I was looking for a way to accomplish the same thing with a For-Next loop. Here is what works:<o></o>
<o>

</o:p>
Application.Goto Reference:="HBPGMaster"<o:p></o:p>
Selection.Copy<o:p></o:p>
<o:p></o:p>
Application.Goto Reference:=”HBPG15”<o:p></o:p>
ActiveSheet.Paste<o:p></o:p>
Range("A1").Select<o:p></o:p>
<o:p></o:p>
Application.Goto Reference:=”HBPG16”<o:p></o:p>
ActiveSheet.Paste<o:p></o:p>
Range("A1").Select<o:p></o:p>
<o:p></o:p>
Application.Goto Reference:=”HBPG17”<o:p></o:p>
ActiveSheet.Paste<o:p></o:p>
Range("A1").Select<o:p></o:p>
<o:p></o:p>
Application.Goto Reference:=”HBPG18”<o:p></o:p>
ActiveSheet.Paste<o:p></o:p>
Range("A1").Select<o:p></o:p>
<o:p>

</o>
<o></o>
I understand that I can have a For-Next loop step through this process 26 times using an array of the range names, but I’m unsure on the syntax. In addition, I’d rather not have to type each of the range names in VBA.<o></o>
<o></o>
I tried experimenting with using a For-Next loop and having VBA reference a list of the range names contained in one of the worksheets, but I was unsuccessful. Here is what I came up with to test it with 5 of the worksheets (this does not work):<o></o>
<o></o>
<o>

</o:p>
' Declare variables<o:p></o:p>
   Dim c As Variant<o:p></o:p>
   Dim tsr As Range<o:p></o:p>
   Set tsr = Range("U51:U55")<o:p></o:p>
   <o:p></o:p>
' Copies the hours by program grant from Master worksheet<o:p></o:p>
   Application.Goto Reference:="HBPGMaster"<o:p></o:p>
   Selection.Copy<o:p></o:p>
<o:p></o:p>
' Pastes the hours by program grant to each timesheet<o:p></o:p>
' Also de-selects the paste range on each worksheet<o:p></o:p>
   For Each c In tsr<o:p></o:p>
       Application.Goto Reference:=(c)<o:p></o:p>
       ActiveSheet.Paste<o:p></o:p>
       Range("A1").Select<o:p></o:p>
   Next c<o:p></o:p>
<o:p>

</o>
<o></o>
The main issue (I believe) is that my reference is not valid.<o></o>
<o></o>
Any and all help / guidance / direction would be much appreciated. Thanks!<o></o>

 

[Smitty]

Welcome to the Board!

Untested, but something like this:

Dim i as long
 
Range("HBPGMaster").Copy<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
 
For i = 15 to 18 ' Adjust as necessary
  Range(”HBPG&i).Paste
Next i

HTH,

How's Murrieta these days? Been a while since I lived there...

 

[ChrisM]

I'm a little unclear on why you need a macro to do this. If you are just copying and pasting (and not paste-values), then why not just permanently link each of the 26 sheets back to your master sheet with

=Master!A1
etc

 

[Jeff Meyers]

Welcome to the Board!

Untested, but something like this:

Dim i as long
 
Range("HBPGMaster").Copy<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
 
For i = 15 to 18 ' Adjust as necessary
  Range(”HBPG&i).Paste
Next i

HTH,

How's Murrieta these days? Been a while since I lived there...

Thank you for the direction, but it's not working - the paste range has an error. I thought that it's missing a quote so I tried inserting it and it still doesn't work.

I tried:

Range("HBPG"&i).Paste    'which I thought would work

and even tried:

Range("HBPG&i").Paste

Not sure how long is a while, but Murrieta is probably pretty much the same - only larger, but the growth has definitely slowed. The population is now over 100k and Loma Linda opened a hospital here last year.

 

[Smitty]

Check your named ranges. They need to match what you have in the code.

This is the snippet that you want:

Range("HBPG" & i).Paste

If you have the "&i" within the quotes then VBA sees that as part of the HBGP text and can't iterate through the loop.

 

[Jeff Meyers]

I'm a little unclear on why you need a macro to do this. If you are just copying and pasting (and not paste-values), then why not just permanently link each of the 26 sheets back to your master sheet with

=Master!A1
etc

Some of the infomation on each sheet is linked (i.e. employee name, number, etc.).

The worked hours by program grant are hard coded as a "template". Each pay period, the employees will adjust that particular sheet for any holiday, vacation, or sick hours, and will delete the "template" hours if that makes sense.

Thank you for the suggestion.

 

[Jeff Meyers]

Check your named ranges. They need to match what you have in the code.

This is the snippet that you want:

Range("HBPG" & i).Paste

If you have the "&i" within the quotes then VBA sees that as part of the HBGP text and can't iterate through the loop.

I checked and double checked my named ranges. They matched and it still did not work. I believe that the issue was because the named ranges are on other worksheets.

I changed the code to the following and now it works:

' Declare variables
    Dim counter As Long
            
' Copies the hours by program grant from Master worksheet
    Range("HBPGMaster").Copy
   
' Pastes the hours by program grant to each timesheet
' Also de-selects the paste range on each worksheet
    For counter = 15 To 26
        Application.Goto Reference:="HBPG" & counter
        ActiveSheet.Paste
        Range("A1").Select
    Next counter
    
    For counter = 1 To 14
        Application.Goto Reference:="HBPG" & counter
        ActiveSheet.Paste
        Range("A1").Select
    Next counter

Thank you for the direction - it was a big help.