Hi All,
I have code that is set to produce multiple workbooks by copying two specific worksheets from a master workbook. That portion of code, I worked out and have working well.
The second piece is that I need to like a Range of Cells (Same Row, Adjacent), back to a Rollup Worksheet in the Master. When I added the code to Copy+Paste the links from the newly created Workbook, back to the Data Table in the Master, i began getting a Compile Error on the Next Statement (Next without For). I suspect it has something to do with changing Active Windows.
Any help or direction is greatly appreciated.
Thanks
J
I have code that is set to produce multiple workbooks by copying two specific worksheets from a master workbook. That portion of code, I worked out and have working well.
The second piece is that I need to like a Range of Cells (Same Row, Adjacent), back to a Rollup Worksheet in the Master. When I added the code to Copy+Paste the links from the newly created Workbook, back to the Data Table in the Master, i began getting a Compile Error on the Next Statement (Next without For). I suspect it has something to do with changing Active Windows.
Any help or direction is greatly appreciated.
Thanks
J
Code:
Sub CreateJobSheets()
' Copy the Checklist Data to the HOLD WORKSHEET to ensure the Job-list is in place
Worksheets("HoldData").Visible = True
Worksheets("Master Checklist").Select
Range("$B$11:$CX$210").Select
Selection.Copy
Worksheets("HoldData").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Worksheets("HoldData").Visible = False
Dim ListSh As Worksheet, BaseSh As Worksheet
Dim NewSh As Worksheet
Dim JobList As Range, LRow As Long, Cell As Range
Dim mNAME As Workbook
mNAME = Application.GetOpenFilename
With ThisWorkbook
Set ListSh = .Sheets("JobData")
Set BaseSh = .Sheets("Tracking")
Set DataSh = .Sheets("JC-Billing Link")
End With
LRow = ListSh.Cells(Rows.Count, "B").End(xlUp).Row
Set JobList = ListSh.Range("B2:B" & LRow)
With Application
' .ScreenUpdating = False
.Calculation = xlCalculationManual
End With
For Each Cell In JobList
BaseSh.Copy After:=Sheets(Sheets.Count)
Set NewSh = ActiveSheet
With NewSh
On Error GoTo Error
GoTo Continue
Error:
.Name = "Dup" & Cell.Value
.Tab.ColorIndex = 25
Continue:
On Error GoTo 0
.Range("$AG$9") = Cell.Value
.Calculate
End With
With Worksheets("CoverSheet")
.Select
.Copy After:=Sheets(Sheets.Count)
.Range("$G$2") = Cell.Value
.Calculate
End With
Sheets(Array("CoverSheet (2)", "Tracking (2)")).Select
Sheets(Array("CoverSheet (2)", "Tracking (2)")).Move
Dim fName As String
fName = ActiveWorkbook.Worksheets("Tracking (2)").Range("$AF$1") & ".xlsm"
With Worksheets("Tracking (2)")
.Select
.Name = ("Tracking")
End With
With Worksheets("CoverSheet (2)")
.Select
.Name = "CoverSheet"
End With
ActiveWorkbook.SaveAs fName _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
' Code to LINK the JobCost and Billing Data back-to the Master Event Workbook
' WHEN I ADDED FROM HERE TO "ActiveWindow.Close" the Sub began giving me the Compile Errror
With ActiveWindow
Set DataL = .Sheets("Tracking")
End With
With DataL.Range("$E$9:$M$9")
.Select
.Copy
End With
mNAME.Activate
Dim endRow As Long
With ActiveWorkbook.Worksheets("JC-Billing Link")
.Select
endRow = Range(.Range("A7"), .Range("B206").End(xlUp)).Row
Range("B" & endRow).Select
.Paste Link:=True
DataL.Activate
Range("F26").Select
ActiveWorkbook.Save
ActiveWindow.Close
Next Cell
With Application
' .ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
Worksheets("SetUp").Select
MsgBox "Job Workbooks for Event Created"
End Sub