Code names for excel sheets

[alan82]

Hi,

I am using the code names method to reference sheets from a Macro,
this works great when I have only one workbook open.

But, I would like to copy all the sheets into a new workbook, do some
stuff with the data in the new workbook (my problem is here!), save
the new workbook and then return to the original workbook.

Once I have copied all the sheets into a new workbook (so there code
names are identical) I try to used the following code which does not
work:

Windows(NewWindowName).Activate
Summary.Select
Call myMethod

Note: NewWindowName is the filename of the new workbook; and Summary
is the codename for one of the sheets in both the old and new
workbook.

I would rather use the codenames so that the method is dynamic.

Please does anybody know how to fix this??

I have tried things like Activeworkbook.Summary.Select with no
success....

Thanks in advance for your time.

A

 

[Bob Phillips]

Windows(NewWindowName).Activate
With wb.VBProject

.VBComponents(.VBComponents("Summary").Properties("Codename")).Properties("Name").Select
End With
Call myMethod

 

[Bob Phillips]

wb refers to the activeworkbook here BTW

 

[alan82]

Apologies, I have not explained my problem clearly....

This method still requires that I make some static reference to the
sheet name.

I can just use:

Sheets("Summary").select

instead. The reason that i want to use the code name is because I have
other sheets that show information corresponding to a date. The sheet
name will change according to date, but the code name will not. e.g. I
have the following codenames for sheets.

Summary
Yr1
Yr2
Yr2

The actual name that is displayed on the tab in excel will vary for
sheets Yr1, Yr2, Yr3. Hence I would like to avoid making a static
reference to the name... my code will not be dynamic in this case.

Can I retrieve the codename for a sheet without using the name that is
displayed on the sheets tab?

 

[Bob Phillips]

No, Summary is the codename in this example. You cannot reference a sheet in
another workbook by its code name so I gave you code to retrieve the Excel
sheet name from the code name, and then use that.

 

[alan82]

ok, this is clear now.

thanks for your help!