Complie Error within FOR NEXT

VSquared

New Member
Joined
Jun 4, 2014
Messages
9
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



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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
Code:
            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

You don't have an end with here. I think that's your issue.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,986
Messages
5,526,065
Members
409,685
Latest member
Davetom

This Week's Hot Topics

Top