MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Selecting the same range on multiple pages...

Posted by Todd Kasenberg on July 16, 2001 5:02 PM

I have a workbook which contains multiple sheets - each sheet contains a range which the user should be able to print. However, while the occupied space on each sheet is the same, the content is not.
Is there a way I can produce a button on each sheet that allows the user to select the range (i.e. same area RXC), without having to name each individual range?

Posted by Skewer on July 16, 2001 5:42 PM

potentially long winded but...

Depending on whether the range is contiguous (ie
a4:d30) or broken into several little areas, and
presuming you just want the cell automatically
selected (no fancy "paste special" commands and
such), just use a macro and a form button:

Create your macro by recording as you highlight
cells or just write it - use

sub myrange()
end sub

where all of the ranges are on the current sheet.
Now if you make twenty buttons on different sheets
you can assign the macro (myrange) to each with a
right click on the button itself. If you want a
button that selects these ranges on all relevant
sheets, use something like this as the first line
of the macro

Sheets(Array("Mon", "Tue", "Thu", "Fri")).Select

where the days are replaced with your desired
sheet names as they appear on the tabs in your

Sorry if this is too simplistic or whatever, hope
it helped.