Solved Cannot rename tabs on my spreadsheet using dates

Microsoft Excel 2010 - complete product...
April 26, 2012 at 04:45:43
Specs: Windows 7

I picked up a macro from another forum that i want to use for renaming the tabs on my "weekly planner" spread sheet. Basically it's a planner that we re-use every year so each year i just want a simple way of renaming each tab to update it with the "week commencing date" for the 52 weeks of the year.

The macro i'm using is this:

Sub RenameSheets()
Dim nxtName As Integer
For nxtName = 1 To Sheets.Count - 1
Sheets(nxtName + 1).Name = Sheets(1).Cells(nxtName, 1)
End Sub

It works well if you just want to rename the sheets (sheet1, sheet 2 etc) but if you try to insert dates it doesn't allow it due to the dates automatically containing / which isn't allowed on a tab.

I am a major amatuer at Excel so any help on this would be massively appreciated.

I've tried simple things like doing the dates in other cells and then using them as the base and making A1 column (=B1) etc as this means that the cell doesn't contain a / but this doesn't work as i suppose the source cell still contains a /.

Sorry if this sounds majorly stupid but it would save me a lot of time in the long run if i can sort a solution to this.

Many thanks, Adam.

See More: Cannot rename tabs on my spreadsheet using dates

Report •

April 26, 2012 at 05:43:27
✔ Best Answer

I have managed to overcome this by adding .Text after the 3rd line from the bottom.

Hope this helps others with the same problem.


Report •
Related Solutions

Ask Question