Selecting multiple sheets from range values [Closed]

Report
-
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
-
Hello,

I have a workbook with information that distributes over a number of sheets (for the sake of simplicity call them "Home", "One", "Two", "Three", "Four", "Five").

Reports for different functions are compiled by selecting the required sheets (eg the report for Dept1 would be by selecting sheets "One", "Three" and "Four", for Dept2 would be "Two" and "Five").

Currently reports are generated by putting the names of the required sheets in a macro and attaching to a button meaning a new macro for each report or selecting by hand.

I would like to be able to put lists of sheet names into named ranges on page "Home" and have a sub that selects the sheets named within cells eg range 'report1' contains the values

A1 = One,
A2 = Three,
A3 = Four

with a macro that does something like

For Each c In Range("report1")
Sheets(c.Value).Select
Next c

but this method will only select the last named sheet in the range ("Four"). Can I select all the sheets listed in the range. Presumably I would be to use the same selection method to format the named sheets.

Thanks
Chalk

1 reply

Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
use Union
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!