Referencing Different Sheets in VBA
 

Hi,

How can I make refer to a cell in a certain worksheet (say the 3rd one
listed), without referring to it by name, as it will vary? I'd like to
be able to do this in formulas in Visual Basic, so basically say:

Cells(1,1).Value = Sheets("[name]").Cells(1,1).Value

Thanks!
Brett

Referencing Different Sheets in VBA
 

Try this:

Cells(1,1).Value = Sheets(3).Cells(1,1).Value



wrote in message
ups.com...
Hi,

How can I make refer to a cell in a certain worksheet (say the 3rd one
listed), without referring to it by name, as it will vary? I'd like to
be able to do this in formulas in Visual Basic, so basically say:

Cells(1,1).Value = Sheets("[name]").Cells(1,1).Value

Thanks!
Brett

Referencing Different Sheets in VBA
 

That's great!

Is there also a way to count the number of sheets I have?

Thanks!

PCLIVE wrote:
Try this:

Cells(1,1).Value = Sheets(3).Cells(1,1).Value



wrote in message
ups.com...
Hi,

How can I make refer to a cell in a certain worksheet (say the 3rd one
listed), without referring to it by name, as it will vary? I'd like to
be able to do this in formulas in Visual Basic, so basically say:

Cells(1,1).Value = Sheets("[name]").Cells(1,1).Value

Thanks!
Brett

Referencing Different Sheets in VBA
 

Use a variable to record the number of sheets. That way you can reference
as needed.

sh = sheets.count

HTH,
Paul

wrote in message
oups.com...
That's great!

Is there also a way to count the number of sheets I have?

Thanks!

PCLIVE wrote:
Try this:

Cells(1,1).Value = Sheets(3).Cells(1,1).Value



wrote in message
ups.com...
Hi,

How can I make refer to a cell in a certain worksheet (say the 3rd one
listed), without referring to it by name, as it will vary? I'd like to
be able to do this in formulas in Visual Basic, so basically say:

Cells(1,1).Value = Sheets("[name]").Cells(1,1).Value

Thanks!
Brett

Referencing Different Sheets in VBA
 

You can also refer to a sheet by its codename, which is not likely to be
changed by users (as there is really no reason for users to change it). It
is separate from the tab name.

In the VBA editor (specifically the project viewer under Microsoft Excel
Objects) the code name appears first, followed by the tab name in
parentheses.

Sheet1 (TabName)

You can reference by Sheet1.Cells(1, 1).Value regardless of what the tab
name is.



" wrote:

Hi,

How can I make refer to a cell in a certain worksheet (say the 3rd one
listed), without referring to it by name, as it will vary? I'd like to
be able to do this in formulas in Visual Basic, so basically say:

Cells(1,1).Value = Sheets("[name]").Cells(1,1).Value

Thanks!
Brett