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:

2973529736

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:

2974629747

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.

297482974929750

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