PDA

View Full Version : Referencing Worksheets from Macros



Frank Thomas
04-20-2008, 03:35 PM
In an Exel Macro how do you references worksheets other than the current active worksheet without changing the current worksheet?

Ekka
04-20-2008, 08:11 PM
Do you mean the old Excel Macros or VBA? If you mean VBA then you need to use the Sheets object so the following gets the value from cell A1 no matter which sheet you are in:

Sub AccessSheet()

' The value in the brackets is referencing which sheet you are trying to access, either a number or
' the actual name of the sheet
MsgBox ActiveWorkbook.Sheets(2).Range("A1").Value

' Accessing sheet by name
MsgBox ActiveWorkbook.Sheets("Sheet2").Range("A1").Value

End Sub

herzberg
04-21-2008, 01:58 AM
Using a variable is another way:

Sub ReferenceThat()
Dim mySht As Worksheet
Set mySht = Sheets("Sheet2")
Set mySht = Sheets(2)
Set mySht = Sheet2
End Sub By the way, all 3 lines above refer to the same sheet.

Frank Thomas
04-21-2008, 05:38 AM
Thanks for the Help.