Name a Sheet a variable?

robfo0

Active Member
Joined
Feb 19, 2002
Messages
281
Is it possible to make my macros a BIT shorter by defining "Sheets("sheetname")" as a variable in my macro? I know i can use:
variable=activeworksheet.name

then :

worksheets(variable).select

but i cant do it like this?:

Sub test()
Dim DataSheet As Worksheet
Dim CurrFile

CurrFile = ActiveWorkbook.Name
DataSheet = Worksheets("DataSheet")

BackUpRange = DataSheet.Range("a1:a5")
end sub

Just wondering, thanks
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Why do you want to do this?

You are trying to set an object variable to a string variable. I don't think you can do that.

Wait, is this what you want to do?
Code:
Dim DataSheet As Worksheet
Dim BackUpRange As Range
Set DataSheet = Worksheets("Sheet1")
Set BackUpRange = DataSheet.Range("a1:a5")
This message was edited by Al Chara on 2002-04-17 15:12
 
Upvote 0
file names seem to be comfusing in VBA suggest add time to end of whatever file name them covers any reason poss date also,, whats more varialbe kinda off,
 
Upvote 0
If you go into VBA and look at the project explorer it will show you a list of all the worksheets. For example:
Sheet1 (DataSheet)
Sheet2 (Sheet2)
When you change the sheet tab name you just change the name inside ()
You can change the name outside the () by clicking on it and then hitting F4 (to bring up the properties window).
Change (Name) to DataSheet (for example) and now you can do the following:
DataSheet.Range("a1:a5")
instead of Worksheets("DataRange").Range("a1:a5")
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top