vba set sheet tab name = cell value

[chasoe]

Dear Sirs,

I've tried the following codes, but however hard I tried to modify the scripts, there is always errors like error code 1004 :

Please Login or Register  to view this content.

I've tried changing a bit like the following but still encounter error
Sheets(1).Name = ActiveWorkbook.Sheets("Movement").Range("$AC$4").Value

Could someone kindly please assist, as I'm still a beginner in vba.

Thanks in advance.



Would like

[sktneer]

What is in AC4 on Movement Sheet? Is it a valid Sheet Name?
when you get Error# 1004, what's the error msg?

[chasoe]

1. Cell AC4 contains a Date (in MMMYY format), and I wish that worksheet tab name to be same Date as in cell AC4
And in fact, the whole workbook contains 13 worksheets (1 sheet is the "Movement" sheet, while the other 12 sheets are for the respective 12 months), and I wish every year I only need to change the 12 Date cells in worksheet "Movement" so that all other 12 monthly worksheet's tab names can be automatically changed by itself.

2. The error says as translated to English : Application or Object Definition Error

[sktneer]

I am not sure why you get that error. If AC4 contains an invalid sheet name, the error msg would be something like "You typed an invalid name for a sheet or chart..."
Are you sure that AC4 is not empty? That seems to be the only possibility behind the error you are getting.

Also, where have you placed that code? Are you sure that you have placed that code on Sheet Module and not placed on a Standard Module like Module1 and trying to run it explicitly?
Since this is an event code, it should be placed on Sheet Module.

Place the following code on Sheet Module and when you activate that sheet next time, the code will be triggered automatically. And if you get an error, click on Debug on the error window and see which line gets highlighted or you get a custom error msg set in the code.

Please Login or Register  to view this content.

[chasoe]

Thx for your effort.

I've been quite frustrated, and would like if you could inspect my working file as uploaded.

1. My aim is simply to update the "Movement" worksheet for a new fiscal year
(from 4/17-3/18 to 4/18-3/19), so that all the other worksheets can change to correct month/year.

2. I know my previous mistake seems to be that the reference cell AH8 must be a text
(instead of a formatted date cell). Am I right?

Sheets(8).Name = Worksheets("Movement").Range("$AH$8").Value

3.I've already put the codes to the respective sheets, but as I activate the Apr sheet,
it automatically renamed my "Movement" sheet to Apr, making everything a mess.
So I now disable all the codes by adding a leading ' in front for your checking.

Thanks so much if your could further help.

[sktneer]

All you need is this...

For Apr Sheet

Please Login or Register  to view this content.

[chasoe]

I seem to have tried something similar to your codes but failed before.
However, when the Excel file goes to your hand, the codes work miraculously.

Thank indeed !

[sktneer]

You're welcome! Glad it worked in the end.
Thanks for the rep.

When you use Sheets(1), that means you are referring a sheet by it's index# (i.e. position of the sheet in the file). Use this method when the workbook structure is not going to be changed.
You can safely refer a worksheet in the code by it's name or better with it's code name.


If that takes care of your original question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.