G
geebee
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
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