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

jrnyman

Board Regular
Joined
Mar 10, 2002
Messages
105
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.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top