Excel VBA .SaveAs breaking in loop

Question

I have an excel workbook broken out with multiple worksheets, 1 per customer. In my code, I am trying to save each individual customer worksheet as its own excel file. However, the .SaveAs command breaks the second time it triggers in the loop. Any pointers would be fantastic.

Dim SchedWorksheet As Worksheet
Dim SchedWorkbook As Workbook
Dim SchedName As String

Set SchedWorkbook = ActiveWorkbook
Set SchedWorksheet = ActiveSheet
Application.DisplayAlerts = False

For Each Worksheet In SchedWorkbook.Sheets
    If Worksheet.Name = "Instructions" Or Worksheet.Name = "Invoice_Items" 
    Or Worksheet.Name = "Customers" Or _
       Worksheet.Name = "Terms" Or Worksheet.Name = "Dilution_Type" Or 
       Worksheet.Name = "Approval_Status" Or _
       Worksheet.Name = "Carriers" Then
        GoTo NextSched
    End If

    If Worksheet.Name = "Invoices" Then
        'basicScheduleFileName is global set at beginning of program
        SchedName = basicScheduleFileName & "ALL"
    Else
        SchedName = Worksheet.Name
    End If

    'payoutFileName is global set at beginning of program
    Worksheet.SaveAs Application.ActiveWorkbook.Path & "\" & payoutFileName 
    & "\Basic Schedule" & "\" & SchedName, xlOpenXMLWorkbook

NextSched:
Next Worksheet

The error on the second iteration is as follows: Run-time error 1004 'Application-defined or object-defined error'

I have also attempted to run this loop using the SchedWorksheet object in lieu of Worksheet and get the error "method .SaveAs of object _Worksheet failed" on the second iteration.

Question I have code extremely similar to his code earlier in my program that takes a similar dataset and uses an exportAsFixedFormat call to save each worksheet as a PDF. Is there an equivalent for .xlsx? (.csv would be fine as well)

Answer 1

I don't know what value "payOutFileName" has so I left it out of the code. I also don't know the value for basicScheduleFileName so I set it to "Something." You will have to change that to whatever you need to change it too. This works fine when saving to my dir "C\Files" Might be a little buggy for you. Hopefully it will be a start.

Sub asdfghj()
Dim SchedWorkbook As Workbook
Dim SchedName As String
Dim basicScheduleFileName As String
Dim payoutFileName As String
Dim ws As Worksheet
Dim wb As Workbook

basicScheduleFileName = "Something"
Set SchedWorkbook = ActiveWorkbook
Application.DisplayAlerts = False

For Each ws In SchedWorkbook.Sheets
Debug.Print ws.Name
    If ws.Name = "Instructions" Or ws.Name = "Invoice_Items" _
    Or ws.Name = "Customers" Or _
       ws.Name = "Terms" Or ws.Name = "Dilution_Type" Or _
       ws.Name = "Approval_Status" Or _
       ws.Name = "Carriers" Then
        GoTo NextSched
    End If

    If ws.Name = "Invoices" Then
        SchedName = basicScheduleFileName & "ALL" & ".xlsx"
    Else
        SchedName = ws.Name & ".xlsx"
    End If

    ws.Activate

'    SaveAs Application.ActiveWorkbook.Path & "\" & payoutFileName & "\Basic Schedule" & "\" & SchedName, xlOpenXMLWorkbook

    Set wb = Workbooks.Add
    ws.Copy Before:=wb.Sheets(1)
    wb.Sheets("Sheet1").Delete
    wb.SaveAs Filename:="C:\Files\" & SchedName, FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
    wb.Close

NextSched:
Next ws



End Sub

Answer 2

First off, thanks to everyone who took time and brainpower trying to figure out my issue. I finally figured out a fix that works.
First I made sure to get rid of ActiveWorkbook and ActiveSheet references to avoid any confusion in Excel.
Second As @NickSlash pointed out, it was likely that even if my code did work, it would save multiple copies of the same file under different names. So, to solve that while fixing my original issue, I changed my code to copy the worksheets that I need into a new workbook and save them that way:

Dim WS As Worksheet
Dim WB As Workbook
Dim NWB As Workbook
Dim SchedName As String

Set WB = Workbooks("Basic_Schedule-.xls")
WB.Activate

'Application.DisplayAlerts = False

For Each WS In WB.Sheets
    WB.Activate

    If WS.Name = "Instructions" Or WS.Name = "Invoice_Items" Or WS.Name = "Customers" Or _
       WS.Name = "Terms" Or WS.Name = "Dilution_Type" Or WS.Name = "Approval_Status" Or _
       WS.Name = "Carriers" Then
        GoTo NextSched
    End If

    If WS.Name = "Invoices" Then
        SchedName = basicScheduleFileName & "ALL" & ".xlsx"
    Else
        SchedName = WS.Name & ".xlsx"
    End If


    'Copy sheet to another WB
    Set NWB = Workbooks.Add
    WB.Activate
    Sheets(WS.Name).Copy After:=NWB.Sheets(NWB.Sheets.Count)
    NWB.Sheets("Sheet1").Delete
    NWB.SaveAs filename:=basicScheduleFilePath & "\" & payoutFileName & "\Basic Schedule" & "\" & SchedName
    NWB.Close
    WB.Activate

NextSched:
Next WS

Instead of this:

'Copy sheet to another WB
Set NWB = Workbooks.Add
WB.Activate
Sheets(WS.Name).Copy After:=NWB.Sheets(NWB.Sheets.Count)
NWB.Sheets("Sheet1").Delete
NWB.SaveAs filename:=basicScheduleFilePath & "\" & payoutFileName & "\Basic Schedule" & "\" & SchedName
NWB.Close
WB.Activate

Do this -- you can avoid the "Activate" method call, and also if you have a reference to WS as an object, it's redundant to do WB.Sheets(WS.Name) when WS already refers to the same Worksheet.

'Copy sheet to another WB
WS.Copy '## Creates a new workbook with the copied sheet.
Set NWB = ActiveWorkbook
NWB.SaveAs filename:=basicScheduleFilePath & "\" & payoutFileName & "\Basic Schedule" & "\" & SchedName
NWB.Close