reference worksheet by sheet number

[bic]

Can I reference worksheets by sheet number, i.e, the first sheet #1 or the
like, the second sheet #2 or the like, etc instead of
worksheet("worksheet_name")? Thanks.

 

[Joe Fallon]

To my knowledge the answer is No.
You can change the Sheet Name from A,B,C to 1,2,3 and then the name is the
same as the Sheet number but other than that...

You can also use Named Ranges which can be anywhere in the workbook.
HTH

 

[Ken Snell [MVP]]

In EXCEL VBA, Worksheets is a collection with an index, just like other
collections. However, the index is "one"-based, not "zero"-based, so the
first worksheet is index number 1.

Thus, you can reference the first worksheet as
Excel.Workbooks("WorkbookName").Worksheets(1)

How/where are you wanting to use this?

 

[John Nurick]

As Ken says, you can do this. As far as I know
oWbk.Worksheets(1)
will return the first worksheet - but this may not be the first visible
worksheet. For that, try something like

Dim j As Long
Dim oFirstSheet As Excel.Worksheet

With oWbk
For j = 1 to .Worksheets.Count
If .Worksheets(j).Visible = True Then
Set oFirstSheet = .Worksheets(j)
Exit For
End If
Next j
End With


... but you can't be certain that will invariably return the first
visible worksheet.

 

[david epsom dot com dot au]

You can also use Named Ranges which can be anywhere in the workbook.

BTW: From Jet, named ranges are in the same namespace as
Worksheets. If you have a Named Range with the same name
as a WorkSheet, the Excel IISAM will give you the range
rather than the sheet.

(david)

 

[John Nurick]

I'm late for work and can't check this now, but doesn't
MyName$
use a sheet of that name, while
MyName
will use a named range if it exists?

 

[david epsom dot com dot au]

MyName$ use a sheet of that name, while

MyName will use a named range if it exists?

That seems likely, but it raises as many questions as it answers :~),
because I've used $ in jet sql instead of ! as a delimiter:
[sheet1$a1:a2]

(david)

I'm late for work and can't check this now, but doesn't
MyName$
use a sheet of that name, while
MyName
will use a named range if it exists?

 

[Steve Ripley via AccessMonster.com]

A little testing yields that when referencing sheets in this way, they are
returned in the order that they appear on the sheets tab from left to
right. It doesn't seem to matter which one has focus or what order they
were added to the workbook. I moved a few around and the results produced
names according to the rule above, Sheets(1)= left most sheet in the
collection.

 

[John Nurick]

Worksheets(1) is the first sheet in the collection, but this is not
necessarily the same as the first sheet in the tabbed list in the
worksheet window.

Starting with a new workbook containing 3 worksheets, this
Worksheets(1).Visible = False
Worksheets.Add
produces a situation where the sheets on the sheets tab and their
indexes are
Sheet4 - Worksheets(2)
Sheet2 - Worksheets(3)
Sheet3 - Worksheets(4)

In fact even explicitly moving a sheet to the beginning of the
collection doesn't necessarily make it Worksheets(1). Starting with the
workbook above, do
Worksheets("Sheet2").Move Before:=Worksheets(1)
and you'll find that
Worksheets(1) is still the hidden Sheet1.