PDA

View Full Version : Copy all of the sheets in an array to a new workbook



simora
09-15-2019, 12:56 PM
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

Kenneth Hobs
09-15-2019, 01:55 PM
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.

simora
09-15-2019, 03:42 PM
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.

Kenneth Hobs
09-15-2019, 04:03 PM
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.

snb
09-15-2019, 11:55 PM
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'.

simora
09-17-2019, 12:07 PM
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 !