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

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 "

Code:

---

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

---

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.

Code:

---

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.

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.

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.

Quote:

---

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

---

Code:

---

Sub M_snb()
  thisworkbook.sheets copy
  activeworkbook.saveas "Arraybook.xlsx",51
End Sub

---

But even simpler

Code:

---

Sub M_snb()
  thisworkbook.savecopyas "Arraybook.xlsx"
End Sub

---

You're code is trying to copy sheets into an existing workbook "arraybook.xls'.

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 !