Consulting

Results 1 to 6 of 6

Thread: Copy all of the sheets in an array to a new workbook

  1. #1
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location

    Copy all of the sheets in an array to a new workbook

    I'm trying to copy all of the sheets in an array to a new workbook called ArrayBook.xls

    I keep getting an error at this point in the code.

    ThisWorkbook.Sheets(myArray).Copy After:=Workbooks("ArrayBook.xls").Sheets(1)

    Here is the rest of the code

    myArray = Application.Transpose(Worksheets("MonthlyTotals").Range("HX1:HX7"))  
        Application.DisplayAlerts = False
    
        ThisWorkbook.Sheets(myArray).Copy Before:=Workbooks("ArrayBook").Sheets(1) 
        ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & ArrayBook & ".xls"

    I also tried this approach and got an error " Subscript out of range "

    For Each sh In myArray
         sh.Copy After:=Workbooks("ArrayBook.xls").Worksheets(1)
     Next sh

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    There could be several things going on.

    1. Not all sheet names, exists in that array.
    2. The workbook to copy to is not open.
    3. The filename.ext is what I use. You can probably use it without the file extension if Windows Explorer is setup to not show file extensions. I show them on mine.
    4. I don't see the error line in the code. For that first part, try adding back your file extension.

    Here are two ways one can do it. I made mine a bit more generic.
    Sub Test1()    
        Dim myArray, ws As Worksheet, wb As Workbook
        
        Set wb = Workbooks("ken.xls")
        myArray = WorksheetFunction.Transpose(Worksheets(1).Range("A1:A2"))
        
        For Each ws In Worksheets(myArray)
            Debug.Print ws.Name
        Next ws
        
        Worksheets(myArray).Copy after:=wb.Worksheets(1)
        Application.CutCopyMode = False
    End Sub
    
    
    Sub Test2()
        Dim myArray, ws As Worksheet, wb As Workbook
        
        Set wb = Workbooks("ken.xls")
        myArray = WorksheetFunction.Transpose(Worksheets(1).Range("A1:A2"))
        
        For Each ws In Sheets(myArray)
            Debug.Print ws.Name
        Next ws
        
        Sheets(myArray).Copy after:=wb.Sheets(1)
        Application.CutCopyMode = False
    End Sub
    I don't think that mine varies that much from yours. Mine works since I do not have the issues detailed.

    WorkSheets are not always equal to Sheets. e.g. Chart sheets.

  3. #3
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location
    Thanks Kenneth Hobs

    Your code worked when I created totally new worksheets,
    but when I switched over to my sheets, The sheets did not copy.

    I stepped through the code, but there's some issue here on my end.
    I'll have to look at it with fresh eyes tomorrow.
    Not sure what's going on.

  4. #4
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    Are the sheets protected or workbook structure protected? If so, a worksheet Protect with UserInterfaceOnly:=True will often allow code to make changes without the Unprotect, code, Protect method every time.

  5. #5
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    I'm trying to copy all of the sheets in an array to a new workbook called ArrayBook.xls
    Sub M_snb()
       thisworkbook.sheets copy
       activeworkbook.saveas "Arraybook.xlsx",51
    End Sub
    But even simpler

    Sub M_snb()
       thisworkbook.savecopyas "Arraybook.xlsx"
    End Sub
    You're code is trying to copy sheets into an existing workbook "arraybook.xls'.

  6. #6
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location
    Thanks Guys.

    What I did was to have a macro copy the sheet names into a range and then used that range as the Array.
    Same code, except for the new location of the range, but it worked.


    Thanks all !

Posting Permissions

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