Excel 2010 VBA - Use Value of variable as another variable -

this practice follow while writing vba code. set worksheet names without spaces variable names. example have workbook 3 worksheets following names

1 - control

2 - 60 w status

3 - 60 w status pvt tbl

i set worksheets names worksheet variables in following way:

set wb = thisworkbook     set wscontrol = wb.sheets("control") set ws60wstatus = wb.sheets("60 w status")     set ws60wstatuspvttbl = wb.sheets("60 w status pvt tbl") 

now want make dynamic meaning code should loop through worksheets in workbook , store worksheet names without spaces variable names ws*

so have following code far:

sub getwsnames() set wb = thisworkbook dim wsnames() = 0  'loop through workbook , store names wsname in array each ws in worksheets     redim preserve wsnames(i)     var1 = ws.name     var2 = replace(ws.name," ", "")     var3 = "ws" & var2     wsnames(i) = var3     = + 1 next  'loop through array wsnames() , assign each element corresponding workbook names 'for example 'set wscontrol = wb.sheets("control") 'set ws60wstatus = wb.sheets("60 w status") 'set ws60wstatuspvttbl = wb.sheets("60 w status pvt tbl")  end sub 

i know if there way achieve this. can use value of variable variable?

you can reference sheets name worksheets collection.

worksheets("sheet 1") 

if instead want reference them modified names, creating array or dictionary work great. suggest using dictionary. allow reference items key. in example use modified name key , use worksheet object item.

dim myworksheets scripting.dictionary set myworksheets = new scripting.dictionary dim ws worksheet each ws in thisworkbook.worksheets     dim modifiedname string     modifiedname = "ws" & replace(ws.name, " ", "")     myworksheets.add modifiedname, ws next 'you can reference worksheets modified names, through dictionary myworksheets("wscontrol").cells(1, 1) = "hi" myworksheets("ws60wstatus").cells(1, 1) = "there" myworksheets("ws60wstatuspvttbl").cells(1, 1) = "world." 

note need add reference ms scripting runtime in order use dictionaries. add reference, go tools->references , check box.