Thread: Sheet Tab Names

This code renames all the sheets in one go

Code:

Sub RenameSheets()
    Dim rng As Range
    Dim wks As Worksheet
    
    For Each wks In ThisWorkbook.Worksheets()
        Set rng = wks.Range("A1")     'change this to the cell that contains the value
        wks.Name = Format(rng, "dd.mm.yy")   'change this for the actual format you want
    Next
    MsgBox "Done"
End Sub

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

Originally Posted by tony h

This code renames all the sheets in one go

Code:

Sub RenameSheets()
    Dim rng As Range
    Dim wks As Worksheet
    
    For Each wks In ThisWorkbook.Worksheets()
        Set rng = wks.Range("B1")     'change this to the cell that contains the value
        wks.Name = Format(rng, "dd.mm.yy")   'change this for the actual format you want
    Next
    MsgBox "Done"
End Sub

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.

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.

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, please edit your post to add code tags.

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.

There is indeed no VBA in Excel 2008.

Originally Posted by 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.

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.

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