using variables in codenames
 

I am trying to write a code that allow a variable in the code name of a
worksheet.

Example
3 worksheets in the workbook with the following names:
worksheet 1 codename = page1
worksheet 2 codename = page2
worksheet 3 codename = page3

I want to write a code that works like the following:

for x = 1 to 3
worksheets(page x .Name).activate ' or other sheet
comand using codename
range ("a1")= "1"
next x

instead of writing the following
worksheets(page1 .Name).activate
range ("a1")= "1"
worksheets(page2 .Name).activate
range ("a1")= "1"
worksheets(page3 .Name).activate
range ("a1")= "1"

suggestions?

using variables in codenames
 

I'm not entirely clear on your question, but something like the
following may help you out.

Dim CName As String
CName = "sheet1"
ThisWorkbook.VBProject.VBComponents(CName).Activat e

This will activate the sheet whose CodeName is stored in the
CName variable, regardless of what the worksheet name is.

See also www.cpearson.com/excel/vbe.htm and
www.cpearson.com/excel/codemods.htm


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




wrote in message
oups.com...
I am trying to write a code that allow a variable in the code
name of a
worksheet.

Example
3 worksheets in the workbook with the following names:
worksheet 1 codename = page1
worksheet 2 codename = page2
worksheet 3 codename = page3

I want to write a code that works like the following:

for x = 1 to 3
worksheets(page x .Name).activate ' or other
sheet
comand using codename
range ("a1")= "1"
next x

instead of writing the following
worksheets(page1 .Name).activate
range ("a1")= "1"
worksheets(page2 .Name).activate
range ("a1")= "1"
worksheets(page3 .Name).activate
range ("a1")= "1"

suggestions?