selecting sheets in VBA

[gfalc3194]

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).

 

[Alphonse68]

Here is the list of sheets:
Index > Tab > Code
1 > Bills > Sheet1
2 > BCs > Sheet2
3 > Bdg > Sheet3
4 > Sch > Sheet4
5 > Cs > Sheet5
6 > Rv > Sheet6
7 > DS > Sheet7
8 > Mmb > Sheet8
9 > LTY > Sheet9
10 > Qs > Sheet10
11 > Rec > Sheet11
12 > Ing > Sheet12
13 > ILst > Sheet13
14 > Meats > Sheet14
15 > Cons > Sheet15
16 > Sup > Sheet16
17 > Alc > Sheet17
18 > Cal > Sheet18
19 > Tx$€ > Sheet19
20 > Meas > Sheet20
21 > Calc > Sheet21
22 > $vs$ > Sheet22

For the new wb they were the standard 1 > Sheet1 > Sheet1, 2...
I also tried copying all the pages from my 22-page workbook to a new book by two methods (and please do not laugh):
Move or Copy... (new book),
Copied the columns of each page to a new page of a second new workbook... yes, time-consuming shot in the dark...
The results were the same, as was the output above.
(The only benefit was that the workbook lost a considerable amount of weight in the move... I guess Excel stores a lot of junk over time. Well, that stands to reason considering the poor house-cleaning skills that Microsoft has overall.)

 

[MARK858]

And the result with the other code?

 

[Alphonse68]

If you mean using Sheet3 (or any number greater than 1) in any other code, it results in the same error.
I tried this on a new wb and on one of the new copies of my wb

Sub SheetProperties()
  Dim wsName as String, wsCodeName as String, wsIndex as Integer
  Debug.Print "Index > Tab > Code"
  wsIndex = Sheet2.Index
  wsName = Sheet2.Name
  wsCodeName = Sheet2.CodeName
  Debug.Print wsIndex & " > " & wsName & " > " & wsCodeName
End Sub

and failed.

If I change the 2's to 1's, for my wb I get
Index > Tab > Code
1 > Sheet1 > Sheet1
eventhough the tab name is "Bills"... go figure!

 

[MARK858]

No I meant the 2 codes I posted to test one loops though the Sheets collection and the other though WorkSheets. I when to know if they give the same result.

 

[Fluff]

If I change the 2's to 1's, for my wb I get
Index > Tab > Code
1 > Sheet1 > Sheet1
eventhough the tab name is "Bills"... go figure!

Possibly a stupid question, but what workbook do you have the code in?
It's not in a personal workbook by any chance?

 

[Alphonse68]

Fluff, the question is never stupid... the answer, however... well, it just makes me feel stupid. Yes, it is by "some" chance in my personal workbook... and as many times as I've had to specify ActiveWorkbook or change This to Active, it never dawned on me that I was missing that part... and I have it right there in the other routine!!! That explains ALL the weird results.

I created a Module in my workbook with 22 pages and the code works just fine, of course!

Well, as I stated before, there was benefit in weight reduction from copying all the sheets to a new workbook (I even found some hidden namedranges when I tried to run a macro to copy them from the old wb to the new), and now I got an added benefit: a refreshing slap on the face to remind me that what might seem obvious often times isn't. Thanks for the wake-up call, Fluff. (I'm over here laughing by myself in embarrassment.)

Thank you for the help, everyone, and I'm sorry I took your time for what turned out to be such a triviality.

Cheers to all.

 

[Fluff]

Unfortunately "The Obvious" is only that in hindsight.

Glad it's sorted & thanks for the feedback