Excel VBA: save multiple sheets in a new workbook
I am looking for a way to save a list of worksheets in a new workbook. This worked well:
ThisWorkbook.Sheets(Array("a1", "a2")).Copy
ActiveWorkbook.SaveAs Filename:="myFile.xlsx", FileFormat:=51
However, the list of worksheets is always different, so in this case I created a string variable Pgs
with text in brackets "a1", "a2"
.
So it looked like this:
ThisWorkbook.Sheets(Array(Pgs)).Copy
ActiveWorkbook.SaveAs Filename:="myFile.xlsx", FileFormat:=51
Does not work. I've tried different options with adding and removing quotes from the start and end ect. I found threads that mention one page from a string variable, but not as many pages as in my case.
Any suggestions?
source to share
The idea is sound, but the problem is this:
Array(Pgs)
Creates a single array of indices that looks like this:
Array[0] = "a1", "a2"
So if your sheet is not named "a1", "a2"
it won't work.
However, this should work:
Pgs = "a1,a2"
ThisWorkbook.Sheets(Split(Pgs, ",")).Copy
Because the function Split
returns a comma separated array. In this case, it Split(Pgs)
will have an array like:
- Array [0] = a1
- Array [1] = a2
source to share