Excel VBA - select multiple sheets

[JimmyEdwards]

Hi all,

I am trying to find a way to select multiple sheets within a workbook as part of a macro. The sheet names and the number of sheets are variable.

I can do it like this:-

Sheets(Array("Sheet3", "Sheet2", "Sheet1")).Select

except that the sheets could have different names each time I use the macro.

Any help would be greatly appreciated.

James.

 

[Loomah]

Hi James
This sounds a little vague to me if "sheets could have different names each time I use the macro"

However if the sheets will always be in the same order then you could try something like

Sheets(Array(Worksheets(1).Name, Worksheets(3).Name)).Select

Happy Friday

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[KenWright]

Just out of curiosity, what is it you are doing that necessitates selecting the sheets?

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[combo]

Aside of selecting array of sheets (and Ken is true here...) it is also possible to extend selection (Charts for chart sheet):

Worksheets(Index_Or_Name).Select Replace:=False

combo

 

[JimmyEdwards]

Hi all.

Thanks for all the help.

I'm using a macro to format reports for clients. All the reports are exported and burst from crystal using Visual Cut.

The sheets in the workbook are title with client codes that are a unique list of up to 9 codes for each client.

I need the macro to select all the sheets in the workbook, but as I will be using the same macro for all the clients I won't know how many sheets there are or what they will be called.

I want the same effect as if I held down shift and clicked on each of the sheet tabs in the bottom left of the screen.

Cheers

James

 

[combo]

You can use SelectedSheets collection, it consists all worksheets and chartsheets selected, as objects. You can loop through it with For Each... statement, getting a reference to each sheet.
Note that the parent object is Window, as you can have multiple windows of the workbook, each with different selection.
In case of one window, use (code in the same workbook):

For Each sh in ThisWorkbook.Windows(1).SelectedSheets

combo

 

[Loomah]

To select all the worksheets in the workbook you can use

worksheets.select

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[JimmyEdwards]

Thank you all for your help.

The Worksheets.Select suggestion from Loomah is perfect, so I'll be using that.

Thanks again to everyone!

James.