How to select multiple sheets using macro

[Sri]

Hi,

I want to select multiple sheets using macro in the
runtime.
I tried recording the action and it gave the following
code:
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select

Actually i want to copy all the sheets in the current
workbook and move it to a new workbook..

Hence the next line would be:
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).copy..

which would copy the selected sheets to a new workbook..

Now the problem is, the sheet names mentioned in the code
are fixed,, whereas i want to select the sheets
dynamically in the runtime..since the sheet names could be
changed in future..

I have tried running a loop to get all the sheet names and
put it in Array function, which didn't work at all..

Does anyone know how to modify this code to select sheets
dynamically

or

any other solution to do the same.

Any help would be highly appreciated.

Regards
Sri

 

[merlin]

Hi,

I want to select multiple sheets using macro in the
runtime.
I tried recording the action and it gave the following
code:
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select

Actually i want to copy all the sheets in the current
workbook and move it to a new workbook..

Hence the next line would be:
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).copy..

which would copy the selected sheets to a new workbook..

Now the problem is, the sheet names mentioned in the code
are fixed,, whereas i want to select the sheets
dynamically in the runtime..since the sheet names could be
changed in future..

I have tried running a loop to get all the sheet names and
put it in Array function, which didn't work at all..

Does anyone know how to modify this code to select sheets
dynamically

or

any other solution to do the same.

Any help would be highly appreciated.

Regards
Sri

You can cycle through your sheets collection using Sheets(1), Sheets(2),
etc.

 

[Bernie Deitrick]

Sri,

If you want to copy the whole workbook, you can simply use

Activeworkbook.SaveCopyAs "New File Name.xls"

or use SaveAs if you want to simply continue working on the new workbook....

HTH,
Bernie
MS Excel MVP

 

[Dave Peterson]

And one more:

sheets.copy

or even
worksheets.copy
(to avoid chartsheets and other non-worksheets)