Sheet Tab Names

[wella]

I have numerous Sheets which are named using dates from 01.12.09 right through to 30.11.10. The 1st sheet has a cell in B1 containing the date and that sheet is named after the inputted date. This applies to all the other sheets which are calculated from what is input in the 1st sheet and each individual sheet is renamed manually to coincide with the inputted date. Each sheet is identical in format.

Is it possible to have the Sheet names automatically named when the 1st date on the 1st sheet is input? I ask as I have to update the sheet each year and renaming each tab is a chore!!

[tony h]

This code renames all the sheets in one go

Please Login or Register  to view this content.

[tony h]

Just realised that you said cell B1 ... this code is corrected

This code renames all the sheets in one go

Please Login or Register  to view this content.

[royUK]

What sort of data are you storing. If it can be placed into a table Format - one row for each day, then use VLOOKUP to populate a sheet for viewing it would be much easier to manage.

[wella]

Thanks Tony.
Only ever put a few lines of code in before and that was in Excel 2004 using VB editor. I'm now using Excel 2008 and don't know where to put it. Sorry!
Oh and I use the Mac version.

[tony h]

FIRST MAKE A COPY OF YOUR SPREADSHEET just in case

Then:
To get to the code section from a normal worksheet:
- click on the "Developer" tab
- click on "view code"

This will bring up the code section

- click on "insert" ... "module"

- in the main part of the screen just under "(general)" paste in the code from this post

Now the code is in your workbook
- click on the line "Sub RenameSheets()" in fact any of the lines below will do just as well.
- press F5 - this will cause the macro to run

Let us know how you get on ... there are some reasons why the code might not run but it will be easier if you say what happens than if I try and list every possible problem.


I think this youtube is for Excel on the mac. It might give you a sense of the VBA screens.I haven't looked at it properly.
http://www.youtube.com/watch?v=nDsAwG87Wa0

[Larry.LeBlanc@O]

If you are unfamiliar with VBA, this might be easier to read. It works. It's the same idea as originally posted with fewer abbreviations.
Sub RenameSheets()
For Each Worksheet In Sheets
MyDate = Worksheet.Range("A1").Value 'change this to the cell that contains the value
Worksheet.Name = Format(MyDate, "dd mm yy") 'change this for the actual format you want
Next Worksheet
End Sub

[shg]

Larry, please edit your post to add code tags.

[tony h]

Dear Wella,

I suggest you follow my original. Proper variable declaration (they are not abbreviations) makes for more robust code. By all means use longer variable names but do declare them. And my advice is don't use a name that is a class property or method.

It is also good practice (in the Code window) to go to Tools. Options and ensure that "require variable declaration" is ticked, This puts the line "Option Explicit" at the top of each module.

[wella]

Tony, i've searched for the developer tab everywhere but to no avail. Apparently VB is no longer used in Excel 2008 Mac version and has been replaced by Applescript which leaves me in the wilderness!!

Excuse my ignorance but although I understand a little coding I haven't delved into it very much. Maybe i'm looking in the wrong place and hopefully you can shed some light on it to resolve the issue.

Thanks in advance.

[shg]

There is indeed no VBA in Excel 2008.

[tony h]

Tony, i've searched for the developer tab everywhere but to no avail. Apparently VB is no longer used in Excel 2008 Mac version and has been replaced by Applescript which leaves me in the wilderness!!

Excuse my ignorance but although I understand a little coding I haven't delved into it very much. Maybe i'm looking in the wrong place and hopefully you can shed some light on it to resolve the issue.

Thanks in advance.

A Mac That leaves me in the wilderness.

If shg says "There is indeed no VBA in Excel 2008" on the MAC I would assume he knows. I have no idea if Applescript has access to the object model.

Maybe use a friend's PC to run the code; but I have no useful experience of transferring between Mac and PC.

[royUK]

Mac's no longer have VBA,so you can't transfer