VBA Runtime Error 9 - Trying to copy same sheet from multiple workbooks

[kristiknyc]

Hi there! I'm just getting started with VBA and haven't been able to find a similar answer to my question using Google.

I'm attempting to create a macro that has the ability to copy and paste a specific sheet from multiple open workbooks to one, fully compiled open workbook. Here's what I have so far:

Sub PullTabfromOpenWBs()
' PullTabfromOpenWBs Macro
Dim wb As Workbook
Dim ws As Worksheet
'Copy and Paste BOM into Excel sheet
         For Each wb In Application.Workbooks
         If wb.Name <> "PERSONAL.xlsb" Then
wb.Sheets("SPECIFICSHEETNAME").Copy _
        After:=Workbooks("OPENWORKBOOKNAME").Sheets.Count
        End If
        Next wb
End Sub

The error message that I'm getting currently when I try to run it is Run time error 9/Subscript out of range with the below portion highlighted and the arrow pointing to the second line:

wb.Sheets("BOM-Detailed Components").Copy _
        After:=Workbooks("Excel Pull from All Open Workbooks.xlsx").Sheets.Count

I'm working on the excel version listed below:
Microsoft® Excel® for Microsoft 365 MSO (Version 2204 Build 16.0.15128.20158) 64-bit

Thank you very much in advance for help and for your patience with me

- Kristi

[Paul_Hossler]

Not tested, but try / start with this

You also have to deal with any Add Ins and any hidden workbooks

I think the issue was with the way you specified the After:= worksheet

Option Explicit


Sub PullTabfromOpenWBs()
    Dim wb As Workbook
    Dim ws As Worksheet, wsFrom As Worksheet, wsAfter As Worksheet


    For Each wb In Application.Workbooks
        If LCase(wb.Name) = "personal.xlsb" Then GoTo NextWB
        If wb.IsAddin Then GoTo NextWB
        If Not Windows(wb.Name).Visible Then GoTo NextWB
            
        Set wsFrom = wb.Sheets("BOM-Detailed Components")
        Set wsAfter = ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
        wsFrom.Copy After:=wsAfter
        
NextWB:
    Next wb

End Sub

[kristiknyc]

Hi Paul,

Thank you for the response! That makes sense.

I've tried it and am still getting the below error:

error.PNGerror code - vba highlighted.jpg

I have my target workbook open as well as my two other WB's with a sheet called "BOM-Detailed Components" in each one that I'm trying to copy over into my current WB

[Paul_Hossler]

I'm guessing that there is no worksheet by that name in the 'Copy From' workbook

Check name, including leading, trailing, or multiple spaces

[kristiknyc]

Hi Paul,

Thanks again for your response.

Ah, the workbook I had open and was trying to paste the tabs into seemed to be the issue so I added a placeholder worksheet with the correct name added which seemed to get rid of the subscipt 9 error code. However, now I'm presented with the below:

error code.PNGvba error.jpg

Is this a syntax issue on my end?

Not sure if this provides any insight but I set up a test version of the files I'm trying this on and have attached here too. "Test" files are the ones I'm trying to copy a specific sheet from and "Excel Pull from all..." is the one I'm trying to copy into.

test.xlsxtest2.xlsxExcel Pull from All Open Workbooks.xlsm

Much appreciated,

Kristi

[kristiknyc]

Oh, I just tried copying and pasting the macro from my personal workbook into my target workbook itself and that seemed to do the trick! I imagine there was an issue with my workbook being hidden before when it was related to my PERSONAL.XLSX?

Anyways thanks so much for the VBA help and for solving my issue

[Paul_Hossler]

Try this and see

Option Explicit




Sub PullTabfromOpenWBs()
    Dim wb As Workbook
    Dim ws As Worksheet, wsFrom As Worksheet, wsAfter As Worksheet




    For Each wb In Application.Workbooks
        If wb Is ThisWorkbook Then GoTo NextWB
        If wb.IsAddin Then GoTo NextWB
        If Not Windows(wb.Name).Visible Then GoTo NextWB
            
        Set wsFrom = wb.Sheets("BOM-Detailed Components")
        Set wsAfter = ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
        wsFrom.Copy After:=wsAfter
        
NextWB:
    Next wb


End Sub