Consulting

Results 1 to 7 of 7

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

  1. #1

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

    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
    Last edited by Aussiebear; 05-09-2022 at 03:02 PM. Reason: Added code tags to submitted code

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    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

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    I'm guessing that there is no worksheet by that name in the 'Copy From' workbook

    Check name, including leading, trailing, or multiple spaces
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    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

  6. #6
    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

  7. #7
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •