Changing VB Component Names to match Worksheet names using VBE

P

Philip

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
 
P

Philip

I got it... simplicity itself:

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

thx anyway
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top