Macro begins but won't continue loop

[Scott02]

Hi,
Before I ask, I've got to acknowledge I know nothing about VBA except what I've picked up trying to understand what they do. I recently copied a working macro to a new file and tried to duplicate the process by editing certain lines. Now, the first half works, but the For i= piece to loop through a string of numbers doesn't even start. The purpose of the macro is to change a cell value which allows calculations to run on a worksheet, then copy the worksheet to a new book and paste, then copy paste values. The loop should then update the reference cell to the next value and repeat by adding that sheet to the new workbook created in the first round. For some reason, it simply doesn't start the second part of the macro, but I receive no error message. Any advice would be appreciated. Cell E1 is the point of reference for VLOOKUPs in the worksheet. Cell G1 is the first number of the range I want use. For the loop, Cell H1 adds 1 to the value of G1, and Cell H2 is the max of the range. Here is the entire macro:

Sub Create_Individual_Reports()

Application.ScreenUpdating = False
Windows("02-2012 Ytd Branch Office Summary.xls").Activate
Sheets("lookup").Select
Range("E1") = Range("G1")
Sheets(Array("Individual Rep Summary")).Select
Sheets(Array("Individual Rep Summary")).Copy
ActiveWorkbook.SaveAs Filename:="C:\Individual Reports.xls"
Sheets(Array("Individual Rep Summary")).Select
Cells.Select
Range("A1").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Individual Rep Summary").Select
Sheets("Individual Rep Summary").Name = Range("A2")
Cells.Select
Application.CutCopyMode = False


For i = Range("H1") To Range("H2")
Windows("02-2012 Ytd Branch Office Summary.xls").Activate
Sheets("lookup").Select
If Range("E2").Offset(i, 0) = "Y" Then
Range("E1") = i
Sheets(Array("Individual Rep Summary")).Select
Sheets("Individual Rep Summary").Copy After:=Workbooks("Individual Reports.xls").Sheets(Workbooks("Individual Reports.xls").Sheets.Count)
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Individual Rep Summary").Select
Sheets("Individial Rep Summary").Name = Left(Range("A2"), 25)
Cells.Select
Application.CutCopyMode = False
Else
End If
Next i


End Sub

Thanks in advance for any help. Much appreciated!
Scott

 

[Bluejay07]

Hi Scott,

Please note that this forum is for VB 5 & 6 code. You may receive some replies here, although you should direct your code to the vba forum: forum707

If at first you don't succeed, then sky diving wasn't meant for you!

 

[Andrzejek]

I am with Bluejay07, but before you post to the other Forum, test what you have in Range("H1") and Range("H2"):

[blue]
MsgBox "Range("H1") has " & Range("H1") & " and Range("H2")
has " & Range("H2")

[/blue]
 For i = Range("H1") To Range("H2")
    ....

Have fun.

---- Andy

 

[Scott02]

Guys, sorry for posting in the wrong forum. My mistake and I'll try the other one.

Andy, the For i = Range(.... piece is something I changed from the original macro so I could control the start/stop points for running the macro. I may put it back to hard coded within the macro and see if it works. Thanks for the replies.

Scott