Using a cell value to label worksheet tabs

[Wildland]

I want to use a progressing cell value (a date) shown in the same cell (A,4) on multiple sheets, e.g. 1/1/05 through 1/14/05 on 14 sheets as the label for these sheets. That is, page one is 1/1/05, page two is 1/2/05, etc. There is a 15th sheet at the end I do not want to label by this method. VBA code is required and I've seen some examples but none seem to work.

I am not a programmer so I'll need some basic how-tos in the VBE.

Thanks,

Wild
[/code][/quote]

 

[clares]

What about something like this. You could put this in your This Workbook, and place it on the Workbook_Open?

Sub LoadTabs()
    With Workbooks("Book2.xls")
        For iCnt = 1 To .Worksheets.Count
            Worksheets(iCnt).Select
            .Worksheets(iCnt).Name = Range("A1").Value
        Next iCnt
    End With
End Sub

Kind regards

Peter

 

[chiello]

I think the probles is that "/" is not allowed for Sheets name.

So you can use a date in this way: mm-dd-yyyy

The code below works for that:

For i = 1 To 14
Sheets(i).Name = Format(Month(Sheets(i).Cells(1, 4)), "00") & "-" & _
Format(Day(Sheets(i).Cells(1, 4)), "00") & "-" & Year(Sheets(i).Cells(1, 4))
Next i
End Sub

Post for feedback
Ciao

 

[SydneyGeek]

This will get you started. The first part navigates to the first sheet, teh second renames the sheets. The date format has dashes because the slash character is not allowed.

Sub SheetNames()
Dim i As Integer

On Error GoTo donames
Do 'go to the first sheet
   ActiveSheet.Previous.Select
Loop

donames:
For i = 1 To 14 'name the sheets, with accepatble date format
   ActiveSheet.Name = Format(Range("A4").Value, "d-mmm-yy")
   ActiveSheet.Next.Select
Next i
End Sub

Denis

 

[Wildland]

Appreciate the responses... but not getting anything to work. I should have been clearer, the cell contains January 1, 2005 in A4 on sheet 1 and January 2, 2005 in A4 on Sheet 2 etc. I can separate the cells so only January 1 is in the reference cell if the comma is a problem. I've linked all the cells in the workbook so when I change the date in the first sheet all the rest are advanced by a day.

Do I paste the code to a module? Or just click the "View Code" menu item when I right click on the first sheet tab. Sorry, but as I said I'm not a programmer. I'll need some step by step here. Your patiance is appreciated.

Dan

 

[SydneyGeek]

Dan, your dates are in a valid format so that's not the issue. The problems are (1) you put the code in the wrong location and (2) you need to reformat the date so the sheet tab will accept it -- see my post.
Try this:
Press Alt + F11, then Insert | Module. Paste my code, return to Excel, press Alt + F8 and doubel-click the macro name.

Denis

 

[Wildland]

Denis,

In the following line what do I change the date text inside the " " to so it reflects the January 1, 2005 date format??

ActiveSheet.Name = Format(Range("A4").Value, "d-mmm-yy")

Also I'm assuming I can set the i = 1 To 14 to 1 to 3 or 1 to whatever as long as it matches the number of sheets.

Dan

 

[SydneyGeek]

Dan, this will change the format:

ActiveSheet.Name = Format(Range("A4").Value, "mmmm d, yyyy")

And yes, change the 14 to whatever number you need.

Denis

 

[Wildland]

Denis,

I got it to work but get an error message for the last line of code stating that "Object variable or With variable not set" when I do a Debug.

It doesn't seem to like ...ActiveSheet.Next.Select

Dan

 

[SydneyGeek]

Dan, the number (14 or whatever) needs to be 1 less than total # of sheets. If you try to go to next sheet from the last one, it'll throw the error.
Just checking: do you always need to omit the last sheet (as per original post) or will you sometimes do all sheets? Code can be changed to be adapt to these situations.

Denis