nice easy one...how do you select multiple worksheets in a m

[jrnyman]

I know how to select multiple cells and multiple worksheets, but how do you select a range of worksheets? My worksheets always end up in the proper order with the same sheet to start and to end, but various sheets in between. Can I select the first through last so I can assign properties easier? My primary need is to easily set the page setup to one x one pages for each sheet and then print. Thanks.

[Ian Mac]

Pardon! sorry I'm not sure what you're asking. Bound to be me though.
can you not record what you want to achieve and amend the code?

[jrnyman]

The Example is Sheets named (in order)
StartSheet
Sheet2
Sheet3
Sheet4
Sheet5
EndSheet

Start and End are always there, but the amound of sheets (and their names) changes each time. I would like to be able to select StartSheet THROUGH EndSheet. I tried recording the selection, but ended up with an array with each sheet's name. That's what I'm trying to avoid as I won't know each sheet's name. Thanks for the help.

[Mudface]

The following will select all the sheets in a workbook, hth: -

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Select (False)
Next ws

[jrnyman]

Here's a question to develop a work-around: How can I find out what number corresponds to the order of a sheet? For example: If the StartSheet is always Sheet(2), is there a method and/or property that will return the 2? If so, I could get the number of the first and last sheets and use a simple loop to select everything in between.

[Mudface]

Sorry, I thought you just wanted all the sheets selected. If you want all the sheets, apart from the start and end sheets selected, then run this: -

Dim ws As Worksheet

Sheets(2).Select

For Each ws In ActiveWorkbook.Worksheets
If ws.Index <> 1 And ws.Index <> Worksheets.Count Then
ws.Select (False)
End If
Next ws

[jrnyman]

".Index": That's what I was looking for!!

Thanks Mudface.

[jrnyman]

Dim ws as Worksheet
...
ws.Select (False)
...


What is the "(False)" for?

[Mudface]

From VBA help (basically it allows you to select several sheets in one go, rather than having to use the array that you get from recording the macro):-


expression.Select(Replace)

expression Required. An expression that returns an object in the Applies To list.

Replace Optional Variant (used only with sheets). True to replace the current selection with the specified object. False to extend the current selection to include any previously selected objects and the specified object.