spreadsheet sheet naming

[gilgalith]

Hello all,

I have a spreadsheet that is used for users weekly stats. The sheet names have to be manually changed every week to this format:

Mm-dd-yyyy

Id like to be able to change cell A1 on the first sheet and have A1 on the other sheets to be the next days date. I then want the sheet names to reflect the date in A1 of each sheet. I know how to do the first part but can't figure out the sheet naming part.

Ive messed around with macros and VB code but the dates in A1 need to be in text format and I can't increment the date on the next sheet when its in text format.

Thanks!

[arlu1201]

Why do you need the dates to be in text format in A1?

If you are using a loop to name the sheets, you can use this -

Please Login or Register  to view this content.

[gilgalith]

Thanks for your response. The cell doesn't need to be in text format for me, just what I've read for the macros i've seen.

As far as the code you've provided me, i'm absolutely the most beginner of beginners when it comes to VB and programming so i'm not sure if more code is provided or where to put it.

[smuzoen]

To create the project from scratch follow these instructions
1. Create a New Workbook. With the workbook open hold down the ALT key and hit F11 (ALT F11)
2. This will bring up the VB editor
3. Select Insert from the top menu and then select a Module - this adds a module to your project - it will be called Module1 by default
4. Copy all of the code below and paste it into the open module (arlu1201 gave you the main workings of the code however you need to create a sub to place the code into)

Please Login or Register  to view this content.

5. Close the VB Editor
6. Save the workbook
7. The shortcut key to open the available macro's to run is Alt F8
8. Select renameSheets and press the Run button (renameSheets is what we called the Sub so it is the Macro Name as well)
Hey presto all the tabs in the worksheet are renamed according to the value in A1 on each respective sheet.
Hope that helps. There is a sample workbook that contains the code above however try creating the workbook for yourself. Once created you will need to save it as a Macro Enabled Workbook (.xlsm extension)