CODEWITHSUNDEEP
excelvba
Māris Brants
Māris Brants

Reputation: 31

Excel VBA: Saving several 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 I produced a string variable Pgs with the text in the brackets "a1", "a2" in this case.

So it looked like this:

ThisWorkbook.Sheets(Array(Pgs)).Copy
ActiveWorkbook.SaveAs Filename:="myFile.xlsx", FileFormat:=51

Did not work. I tried different options with adding and removing quotation marks form the beginning and end ect. I found topics where referring to one page from string variable is discussed but never several pages like in my case.

Any suggestions?

Upvotes: 3

Views: 713

Answers (1)

Jason Faulkner
Jason Faulkner

Reputation: 6578

The idea is sound, but the problem is this:

Array(Pgs)

Will create a single index array that looks like this:

Array[0] = "a1", "a2"

So unless your sheet is named "a1", "a2", this will not work.

However, this should work:

Pgs = "a1,a2"
ThisWorkbook.Sheets(Split(Pgs, ",")).Copy

Because the Split function will return an array broken apart by a comma. In this case Split(Pgs) would have an array like:

  • Array[0] = a1
  • Array[1] = a2

Upvotes: 4

Related Questions