Changing Worksheetnames in VBA code

karraj

Board Regular
Joined
Mar 5, 2002
Messages
76
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?
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top