macro & selecting multiple sheets

[kimt]

Ok, I have pondered this and scratched my head until i've lost some
hair.

I have a workbook, this workbook represents a payperiod, with up to 40
sheets named by the employee's name that relates to that sheet (the
data is obviously different, but the format does not differ). I also
have a totals page I use to calculate cost, hours and a few other
issues. The problem is, I need to reformat all of this information for
a payroll transmittal in an entirely different format, I have created a
macro which does this by automatically coping, pasting, and deleting a
sheet one at a time. My questions are 1). Is there a way you can
specify in the macro to select an array of sheets without specifing the
sheet name? example: if you select the first sheet, hold the shift
down as you select the last sheet it selects all sheets inbetween,
however the macro reads
sheets("john","william","mark"..."jane").select. Unfortunatly, the
workbook changes from payperiod to payperiod making this macro
obsolete.
If there is a command like sheets("john":"jane").select i could make it
work by adding standard sheets on the ends of the work book... Any
ideas.

Thanks

Kim

 

[Dave Peterson]

If you do all the sheets in the workbook, you could do:

dim wks as worksheet
for each wks in worksheets
''''
next wks

If you can select the worksheets before running the macro:

dim wks as worksheet
for each wks in activewindow.selectedsheets
''''
next wks

You can select the sheets by clicking on the first and ctrl-clicking on
subsequent.

If they are contiguous, you can select the rightmost and shiftclick on the
leftmost.