Referencing Different Sheets in VBA

  • Thread starter Thread starter brett.kaplan
  • Start date Start date
B

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
 
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
Click to expand...
Click to expand...
 
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
Click to expand...
Click to expand...
Click to expand...
 
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.
 
Back
Top