Splitting a workbook

Hi,

I have a workbook with 27 sheets where 4 (A, B, C, D) of them are reference sheets and the rest are reporting sheets (1-23). I'd like to create 23 individual workbooks that consist of 4 reference sheets A, B, C, D and 1 reporting sheet. Sheet names for reporting sheets are stored in the named range "Subs"
Here is the code I am trying to use but it generates "Subscript out of Range" Error 9.

 Sub CopyPasteComboTabs()

      Dim strSaveName As String
      Dim xPath As String
      
        xPath = Application.ActiveWorkbook.Path
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
    
    For Each c In Range("Subs")
      strSaveName = c.Value
      
    Sheets(strSaveName).Activate    
    Sheets(Array("A", "B", "C", "D", strSaveName)).Select
    Sheets(Array("A", "B", "C", "D", strSaveName)).Copy
      
Next
    Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & strSaveName & ".xls"
    Application.ActiveWorkbook.Close True
    
End Sub

Please Help me understand why it would not work?

How are the reporting sheets named?

PS Can you add code tags when posting code?

Are there any other workbooks open?

Does this work?

Sub CopyPasteComboTabs()
Dim wbSource As Workbook
Dim strSaveName As String
Dim xPath As String
Dim c As Range

    Set wbSource = ActiveWorkbook
    xPath = wbSource.Path
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    For Each c In wbSource.Range("Subs") ' mind need sheet reference for 'Subs'
        
        strSaveName = c.Value

        wbSource.Sheets(Array("A", "B", "C", "D", strSaveName)).Copy
        ActiveWorkbook.SaveAs Filename:=xPath & "\" & strSaveName & ".xls"
        ActiveWorkbook.Close True
        
    Next c
        
End Sub

Where did you get the error?

Did you try adding a worksheet reference for Range("Subs")?

The code works fine for me in a mocked up workbook if I change the For statement to this.

For Each c In Range("Subs")

PS In the code that fails how is xPath declared?

The code should be run from a module, though running it from ThisWorkbook shoudn't make any difference.

As for the error you describe, the only thing I can think that might cause that would be that xPath was incorrectly declared, or there wasn't an active workbook open, both of which seem highly unlikely.

Any chance you could upload a sample workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

PS No need for data other than that in the 'Subs' list.

No problem.

PS A little tip to avoid that type of thing, add Option Explicit at the top of each module. That will force you to declare all variables which might be a little annoying at first but it'll also pick up typos.