Can make my sheet name vba refs cover all modules?

[tonywatsonhelp]

Hi Everyone,

So I was wondering if there is a way to reference thing like sheet names and ranges outside of the macro and still have it know what I mean?

for examples
This is an example of how I might currently write a macro (I know its not real its for example only)

Sub Example1()


Dim sht As Worksheet
Dim mybook As Workbook
Dim total_ch As Variant


Set mybook = ThisWorkbook


Set Cont = ThisWorkbook.Sheets("Control")
[LEFT][COLOR=#222222][FONT=Verdana]Set Sale = ThisWorkbook.Sheets("Sales")[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Set Chart = ThisWorkbook.Sheets("Charts")[/FONT][/COLOR][/LEFT]

Impot = "I6" 
FileLoc = "E4"


total_tl = SimCont.Range(Teams2Impot).Value
Range("A2") = total_tl


End Sub

Then I start a new macro and have to do these bit all over again

Dim sht As Worksheet
Dim mybook As Workbook
Dim total_ch As Variant


Set mybook = ThisWorkbook


Set Cont = ThisWorkbook.Sheets("Control")

Set Sale = ThisWorkbook.Sheets("Sales")
Set Chart = ThisWorkbook.Sheets("Charts")​

Impot = "I6"
FileLoc = "E4"​

is there a way I could have all this in a macro or somewhere and every macro I write in the document know that Cont =

ThisWorkbook.Sheets("Control")??? etc.


please help if you can

Tony
​

<strike></strike>
​

<strike></strike>

 

[Fluff]

Yes it is possible, but (IMO) a better way (when working with ThisWorkbook) is to use the sheet code names.
If you look in the VBA Project window you will see something like
Sheet1(Control)
Where Sheet1 is the code name & it can be used like

Sheet1.Range("A1").copy

 

[tonywatsonhelp]

Hi fluff,
that would be an excellent idea except I'm not just talking about sheet names, I used them as an example but I make lots of statements in my macros.
such as

sheet names,
or thing like

Impot = "I6"

I just wondered if there was a way to set all these up first so I list all my sheet refs, all my range refs, anything I might create to say "this = this"
then every macro either in the document or in the module if its easier knows Impot = "I6" not just the one I typed it into???

please help if it can be done.

Thanks

Tony
​

 

[Fluff]

Have a look here http://www.cpearson.com/excel/scope.aspx