Changing VB Component Names to match Worksheet names using VBE

04-12-05, 05:27 PM

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

04-12-05, 05:37 PM

Philip

I got it... simplicity itself:

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

thx anyway

"Philip" wrote:
[..]

Similar Threads
Look up and match Vendor name in one cell of worksheet from list ofmultiple Vendor names in column of other worksheet I want to Look up and match Vendor name (such as "Baker") written in one cell of current worksheet (Reference Cell) from list of a variety of vendor names in column of other...
Changing Worksheet Names via VBA Hey all, Is there a way to change the name of the worksheet via VBA code? I could do it using a recorded macro, but the extra step I need is to automate the name change. In...
Changing worksheet names I have created a workbook that I want to use as a template to gether monthly statistics. In the template workbook I ahve generically named the worksheets MMM (for month), but...
Change names of files in a folder to match names in Excel Column Hi, I currently have a folder of word documents which have generic name i.e. 040201-1126.doc, 040201-1127.doc and so on, but I need to chang the files names to match those...
document directory names no longer match names of user owners I altered some user names and now though each user continues to have access to their previously owned directories the directory names no longer match the names of their...