Macro begins but won't complete loop process

[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

 

[zelgar]

What is the name of the worksheet the values of H1 and H2 used in the FOR statement? If it's not to be the Individual Rep Summary sheet, you need to put a Sheets("Name of Worksheet").Select command before the FOR statement.

 

[SkipVought]

hi,

You cannot do

 For i = Range("H1") To Range("H2")

You could do

 For i = 1 to 2   'i will represent the ROW NUMBER on some sheet (unknown to me) where H1 & H2 values are
   with Workbooks(("02-2012 Ytd Branch Office Summary.xls").Worksheets("lookup")
      with SomeSheetObject.cells(i, "H")
         'now what do you want to do?

      end with
   end with
 Next

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

BTW, all objects need fully qualified references.

I could see that if H1 & H2 on whatever sheet that needs to be explicitly specified, contains numeric values where H2 >= H1, then it could work.

So what's in Sheet?!H1 & H2???

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Scott02]

Zelgar and Skip,
The name of the worksheet is 'lookup'. I now see what you mean regarding the sheet name. I didn't add it to each of the range references within the For I statement. Prior to getting these responses, I did go in and change it back to hard numbers and the macro works, but not ideally since I'm only doing a certain number of pages at a time. I'll try to update the macro with the sheet names attached to the range statements and see if it works.
Thanks for the replies!

Scott

 

[zelgar]

I'd do the following:
move the FOR Statement down 3 lines to be after the Sheets("lookup").Select

After the Sheets("lookup").Select command define your start and end values of your for loop as the following
istart = range("H1")
iend = range ("h2")
and revise the FOR statement to
For i = istart To iend