selecting sheets in VBA - more info

[gfalc3194]

I'm somewhat new at message boards so forgive me if adding a new post
is not the way this should be done.

My original post is below. What I fail to mention is that there are
more than just 3 sheets in the workbook. It is more like this: sheet1,
sheet2,....up to sheet6. I want to first select and print sheets1
through 3, then select and print sheets 4 through 6 (To start the page
numbering over). I can't just select sheets by number, say
array(1,2,3), because if I add say sheet1b, I will then have four
sheets to print and would have to modify the macro.

thanks again. below is original post.

If I select the first sheet in a workbook, hold shift and go to the
last sheet, all are selected for printing. However, when I record this
function, the VBA code lists all sheets by name.

For instance, If the workbook contains 'sheet1' 'sheet2' and 'sheet3'
the macro code recorded = Sheets(Array("sheet1", "sheet2",
"sheet3")).Select

I would like to know if there is a way to select from sheet1 to sheet3
without explicitly naming them. The problem is that in the actual
workbook, sometimes I add or delete sheets, and sometimes rename them
for clarity. I don't want to have to modify the macro everytime I do
that. (the first and last sheets can always remain the same).
_________________
Thank you in advance for any help.

 

[steve]

I think this was pointed out before, but try
Sheets(Array(Sheets(1).Name, Sheets(2).Name, Sheets(3).Name)).Select

where the 1, 2, 3 are the relative positions of the sheets in the workbook.
Change to 4, 5, 6 to select those sheets.

Add to the array if you want more sheets selected at a time. Just watch the
"(" & ")" signs.

 

[Tom Ogilvy]

No, if you don't know the name of the sheets and you don't know the position
of the sheets (because in both cased, they might change), then you can't
identify which sheets you want to print. You might be able to go to the
codenames of the sheets and get the current name of the sheet from that.
then you would have a list of names, but codenames can be changed as well
(although less likely).

If the sheets have some unique identifying information on them, then you
could loop through the sheets and identify which sheet is which - just as
you are doing visually.

 

[Tom Ogilvy]

Again, if this was apropos (which the OP has stated it isn't), then
Sheets(Array(1,2,3)).Select

would be the cleaner method.