kristiknyc
05-09-2022, 08:16 AM
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
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