FOR LOOP

[Guest]

hi, I am getting an error message "Compile Error: Next without For". WHen
the following code is executed...

Sub Button1_Click()

suffixDate = Format(Now() - 2, "yyyymm")
prefixnew = Sheets("printout").Range("B" & x).Value & "_"
sFileNameNew = prefixnew & "13WS_"
sFileNameNewCopy = sFileNameNew & suffixDate
Dim lastrecord As Long
Dim sourceNew As String


Dim y As Long
For y = 6 To Sheets("printout").Range("D80").Value


Sheets("facility").Range("A3").Value = Sheets("printout").Range("B" & y).Value


'**************************
'put detail data in to detail sheet
'**************************
'clear existing data first
Sheets("detail").Select
Range("A65000").End(xlUp).Offset(0, 0).Select
lastrecord = Selection.Row
If lastrecord <> 1 Then
Range("A1:BG" & lastrecord).Select
Selection.ClearContents
End If

'open source file and copy records
sourceNew = prefixnew & "_DETAILS_13WS_200710"
Workbooks.Open "S:\path...\" & sourceNew
DisplayAlerts = False
If Range("A2").Value > 0 Then 'file not empty
Range(Range("A2"), Range("AH2").End(xlDown)).Select
Range(Range("A2"), Range("AH2").End(xlDown)).Copy

'activate destination file and paste records
Windows("Template_13WS.xls").Activate
Sheets("Detail").Activate
Range("A2").Select
ActiveSheet.Paste


'**************************
'end of put detail data in to detail sheet
'**************************


'facility
'Sheets("pivot").Range("D7").Value = Sheets("lookup").Range("B3").Value
Sheets("pivot").Select
ActiveWorkbook.RefreshAll

'save the active file as the file for the respective facility
Application.ActiveWorkbook.SaveCopyAs Filename:="S:\path...\" &
sFileNameNewCopy & ".xls"


Next y


End Sub

The debuggr highlights the Next y line.

I noticed that before I add this part:

'**************************
'put detail data in to detail sheet
'**************************
'clear existing data first
Sheets("detail").Select
Range("A65000").End(xlUp).Offset(0, 0).Select
lastrecord = Selection.Row
If lastrecord <> 1 Then
Range("A1:BG" & lastrecord).Select
Selection.ClearContents
End If

'open source file and copy records
sourceNew = prefixnew & "_DETAILS_13WS_200710"
Workbooks.Open "S:\path...\" & sourceNew
DisplayAlerts = False
If Range("A2").Value > 0 Then 'file not empty
Range(Range("A2"), Range("AH2").End(xlDown)).Select
Range(Range("A2"), Range("AH2").End(xlDown)).Copy

'activate destination file and paste records
Windows("Template_13WS.xls").Activate
Sheets("Detail").Activate
Range("A2").Select
ActiveSheet.Paste


'**************************
'end of put detail data in to detail sheet
'**************************

the code works fine, but i need this partof the code.

What should I do?

Thanks in advance,
geebee

 

[Don Guillett]

A cursory look suggests that you are actually missing and

end if

 

[George Nicholson]

If Range("A2").Value > 0 Then 'file not empty
This line is missing a corresponding "End If", and it has to be within the
For..Next.

as in this case, "Next Without For" can mean exactly that *or* it can mean
that you have some other unfinished business within a For..Next Loop that
can't be resolved.