Changing Worksheetnames in VBA code

[karraj]

Is there a way to make sure the Worksheetnames are changed in the VBA code as well?
ANy good programming tip for future and how to correct existing VBA codes?

[Jack in the UK]

ActiveWorkbook.SaveAs Filename:="C:My DocumentsJackintheUK 1.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

Does that help?

Also tips.. stick on this site, i spend millions of hours reading and saving playing and learning, the richest source in the work is right here, if your stuck post a question to getthe answer.

wanna really learn answer hundreds of questons will do just that.

[karraj]

I have got some legacy VBA code that has sheet names hardwired in the code. So, whenever any of the Worksheets name in the workbook is changed, I need to change that in the VBA code.
Is there a way to change the VBA code so that whenever user changes teh name of the worksheet, it is automatically changed in the code!
Thanks,
K

[Ivan F Moala]

On 2002-03-13 15:27, karraj wrote:
I have got some legacy VBA code that has sheet names hardwired in the code. So, whenever any of the Worksheets name in the workbook is changed, I need to change that in the VBA code.
Is there a way to change the VBA code so that whenever user changes teh name of the worksheet, it is automatically changed in the code!
Thanks,
K

Not really....BUT what you should do is make
a note of the worksheets CODE name and then
do a Replace........

What I'm trying to explain is that the Tab
name / Sheet name has a Code name...if you
are in the VBA project explorer you will
see these names to the left of the Bracketed
sheet/tab name. eg.

Sheet1(Mysheet1 name on the tab)
Sheet2(Mysheet2 name on the tab)

etc. The code name remains the same even if
you change the sheet/Tab name.

So you should replace ALL references to a sheet
name to the code name eg using the above as
an example your current code may read......

Sheets("mysheet1 name on the tab").Select
ActiveCell.FormulaR1C1 = "23"

Sheets("mysheet2 name on the tab").Select
ActiveCell.FormulaR1C1 = "23"

Sheets("mysheet1 name on the tab").Range("B14").FormulaR1C1 = "66"

changes to;

Sheet1.Select
ActiveCell.FormulaR1C1 = "23"

Sheet2.Select
ActiveCell.FormulaR1C1 = "23"

Sheet1.Range("B14").FormulaR1C1 = "66"


HTH


Ivan

[karraj]

Ivan,
That was a nice suggestion indeed. This replacement will solve my problem, but on the flip side it makes the code less readable - I mean Sheet1 makes less sense than the name of the sheet1
Is there a way to change that to something more userfriendly.
Thanks,
K