Select multiple worksheets using list contained in worksheet

[dianahowe]

Hi, I have a file which will be used as a template for a monthly report for several business units. I want to have a button which will print out a 'pack' of worksheets but it will not be all sheets in the book. The sheet names may vary across the business units, so I want to have a list of worksheet names in a consecutive range of cells and have the book select all of these sheets. My idea was to loop through the cells and collect the sheet names, but I cannot then get that into any acceptable format so I can say sheets.select(rangeofsheets).

Can anyone help me with this please? The sheets to be selected are always going to be consecutive.

Thanks

[GTO]

Greetings Diana,

You did not mention which cells; or if these are filled-in with worksheet names based upon user input, some type of formula, another macros return, or ???.

So, a close-eyes and swing blade: Might we want a userform, where the user could just select the first and last sheets; and print this range of sheets?

Mark

[dianahowe]

Hi, it's just a range of cells, let's say A15:A25 (although that varies depending on the lengh, the first cell is a named range so I can navigate to it easily) and the worksheet names are just typed in (by me to start with, then the user if they want to add/change) one to each cell.
I don't think that would work as an approach as the sheets to be selected are not necessarity all next to each other.
Thanks,

[p45cal]

IF… the cells which contain the sheet names are contiguous (no blanks in the list) and start at the named range Start and there are blank cells all around this list then this single line should do it for you:
Sheets(Application.Transpose(Range("Start").CurrentRegion.Value)).PrintPreviewIf the above conditions don't pertain, then there will be other ways to ensure the list is right.. come back for that. (The code currently does print preview to save paper; you can replace .PrintPreview with .Printout.)

[dianahowe]

That works for me. Thank you!