Copying multiple sheets to 1 workbook

[rwitte]

I am working on a macro that will take info from a list of machines and components for each machine.

Each machine refer to a "master" workbook that will be opened and sheet 1 (the only sheet in each workbook) will be copied to a new ("machine") workbook, each component also refers to its "master" and will be copied to a separate tab in the new machine workbook (there could be up to 20 components per machine).

The first time through, the code works correctly (the machine workbook is created with the first tab populated with the correct file).

When it goes back through to do the next column, I get a "Run-time error '9': Subscript out of range " error at "Sheets(1).Copy After:=Workbooks(wbName2).Sheets(0)" . Researching the error references a nonexistent array element.

Having very limited exposure to arrays, I do not know if using one would be the best approach for my current issue, how I would set it up and use it or what another workaround would consist of.

I am working with Excel 2016, any help would be appreciated.

Sub makeFiles3()
Dim lCol As Long
Dim lRow As Long
Dim wbName As String
Dim wbName2 As String   'name of new workbook- machine workbook
Dim MachName As String  'name of machine
Dim MastLoc As String   'Master Ledger Location
Dim ListLoc As String    'List Location
Dim ListNm As String    'List file Name
Dim filePath1 As String    'path used to open master file
Dim newTabName As String    'New Worksheet Name
Dim wbName3 As String   'name of new workbook- machine workbook without extension
Dim mySourceWB As Workbook  'this workook contains list of names
Dim myDestWB As Workbook    'New workbook name
Dim wkb As Workbook         'name of new workbook
Dim OtherWorkbook As Workbook         'machine level workbook

    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    ListLoc = "C:\Users\T1738RW\Desktop\"
    ListNm = "machine ledger test.xlsm"
    MastLoc = "E:\master ledgers\"
'Adding New Workbook
    For i = 3 To 8
        MachName = Cells(i, 11).Value & "_" & Cells(i, 6).Value
        wbName = "E:\testRobotLedgers\" & MachName
        wbName2 = wbName & ".xlsx"
        wbName3 = MachName & ".xlsx"
        
'Saving the Workbook
                    Set mySourceWB = ActiveWorkbook
                    
                '   Build new file name based
                    
                    lCol = Cells(i, Columns.Count).End(xlToLeft).Column
                    
                    b = 0
                    For a = 17 To lCol
                    b = b + 1   ' sheet number
                    filePath1 = (MastLoc) & Cells(i, a).Value & ".xlsx"
                    
                    newTabName = Cells(i, a).Value
                        If b = 1 Then
                            
                            Workbooks.Open (filePath1)
                            Sheets(1).Copy
                            
                            ActiveWorkbook.SaveAs Filename:=wbName, FileFormat:=51
                            'ActiveWorkbook.SaveAs Filename:=wbName, FileFormat:=52 'format 52 = macro enabled wb
                            ActiveSheet.Name = newTabName
                            
                            ActiveWorkbook.Save
                            
                            Else
                                                       
                            Workbooks.Open (filePath1)
                            
                            'ThisWorkbook.Activate
                            
                            Sheets(1).Copy After:=Workbooks(wbName2).Sheets(0)
                            
                            'Sheets(1).Copy After:=Workbooks("E:\testRobotLedgers\BSLI_S01R01.xlsx").Sheets(Sheets.Count)
                            'Sheets(1).Activate
                            'Sheets(1).Copy
                            
                            'Sheets.Add After:=Workbooks("E:\testRobotLedgers\").Sheets(Sheets.Count)
                            ActiveSheet.Name = newTabName
                            ActiveWorkbook.Save
                            ActiveWorkbook.Close    'Close master Workbook
                            
                        End If
                            
                    Application.DisplayAlerts = False
                    ActiveWorkbook.Close    'Close wbName Workbook
                    ActiveWorkbook.Close    'Close master Workbook
                    
                    Application.DisplayAlerts = True
                    
                   
                   
                    Next a
    
        'ActiveWorkbook.Close
    Next i
    
End Sub

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel...g-multiple-sheets-1-workbook.html#post5059749

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

Note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it.
Please be patient and do not attempt to post the question again.