Referencing Different Sheets in VBA

[brett.kaplan]

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

 

[PCLIVE]

Try this:

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

 

[brett.kaplan]

That's great!

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

Thanks!

Try this:

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

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

 

[PCLIVE]

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

sh = sheets.count

HTH,
Paul

That's great!

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

Thanks!

Try this:

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

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

 

[Guest]

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.