Relative reference to a worksheet...

Posted by Todd Kasenberg on August 13, 2001 9:05 AM

Maybe I am just overthinking it, but I am naming a range dynamically, and in so doing, want to say, essentially, to pick the range on the current sheet. The macro I've written should work on every sheet, and not just select the range on the sheet where I defined the name.
Can anyone help me - is there a cute way of saying "current sheet" as opposed to providing the sheet name, which is limiting in my example.

Posted by Barrie Davidson on August 13, 2001 9:42 AM

Hi Todd, when I want to use "current sheet" I use a string variable defined at the beginning of the macro.

Sub YourSub()
Dim SheetName As String
SheetName = ActiveSheet.Name 'the current sheet
'Your macro
Sheets(SheetName).Activate 'to activate that sheet
'Rest of your macro
End Sub

Hope this helps you out.

Barrie