Changing VB Component Names to match Worksheet names using VBE
 

Hi,

In VBE the worksheets can be identified using a name that never changes
(default Sheet1 etc)

To retrieve that name I can do this:
VBProject.VBComponents(index).name

and I can change it in a similar way.

My problem is this... I am building new by copying entire sheets (which have
lots of data) into a new template - that has a new VB Project.

I want to rename the VB Code Module of each copied worksheet from it's
current default (like Sheet1) to the name it has in the workbook in Excel...

so how, using the VBIDE/VBE Object module can I reference the code module of
a worksheet to change it's name to the name it has in the workbook?

thanks

Philip

Changing VB Component Names to match Worksheet names using VBE
 

I got it... simplicity itself:

For Each objSheet In Worksheets
objSheet.CodeName = VBA.Replace(objSheet.Name, " ", "_")
Next

thx anyway

"Philip" wrote:

Hi,

In VBE the worksheets can be identified using a name that never changes
(default Sheet1 etc)

To retrieve that name I can do this:
VBProject.VBComponents(index).name

and I can change it in a similar way.

My problem is this... I am building new by copying entire sheets (which have
lots of data) into a new template - that has a new VB Project.

I want to rename the VB Code Module of each copied worksheet from it's
current default (like Sheet1) to the name it has in the workbook in Excel...

so how, using the VBIDE/VBE Object module can I reference the code module of
a worksheet to change it's name to the name it has in the workbook?

thanks

Philip