VBA to rename sheet based on cell value in another VBA dependent sheet name

[mushkapush]

Hi there,

I am a total noob at this so be gentle!

I have a sheet that is essentially a calendar, with 1 week per sheet (i.e. Sheet 1 = 1st Jan, Sheet 2 = 8th Jan and so on). I am trying to make it so that all I have to do is type in to one cell the date I want the calendar to start from, and then each sheet and sheet name will automatically change accordingly. I've got the sheet contents auto-populating fine with some basic formulas, it's the sheet names I'm struggling with.

I have successfully managed to get the 1st sheet to rename itself based on the cell value of B3; when I type "1st Jan" into B3, the sheet name changes to "1st Jan", all good! I now need the 2nd sheet to rename itself based on the cell value of B3 in the 1st sheet also and then add 7 days (so B3+7), and then again the 3rd sheet, and so on. So that all I have to do is type the name into B3 in the 1st sheet and the rest will rename automatically. I am struggling to work out how to do this because by its very nature the sheet name of the 1st sheet isn't initially known, so I don't have a sheet name to reference in the VBA for the remaining sheets.


The macro I'm using for rename sheet 1 is below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Name = Format(Range("B3").Value, "dd-mmm")
If Target = "" Then Exit Sub
Exit Sub
End Sub


Hoping this makes sense as I'm somewhat out of my depth!
Thanks!

 

[Joe4]

Welcome to the Board!

Why are you trying to use the "Worksheet_SelectionChange" event procedure? This code will run anytime you select a range on this sheet.
It sounds like you only want it to run when you update cell B3. So you should be used a "Worksheet_Change" procedure instead.

Try this code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim n As Long
    Dim i As Long
    Dim c As Long
    Dim dt As Date
    
'   Exit if cell B3 not updated
    If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub
    
'   Check to see that value in B3 is a date
    If Not IsDate(Range("B3")) Then
        MsgBox "Value in B3 is not a valid date"
        Exit Sub
    Else
        dt = Range("B3").Value
    End If
    
'   Count the number of worksheets in the workbook
    n = Worksheets.Count
    
'   Loop through all sheets except the first and rename then
    For i = 2 To n
        Sheets(i).Name = Format(dt, "dd-mmm")
'       Increment date by 7 days for next sheet
        dt = dt + 7
    Next i

End Sub

 

[mushkapush]

Welcome to the Board!

Why are you trying to use the "Worksheet_SelectionChange" event procedure? This code will run anytime you select a range on this sheet.
It sounds like you only want it to run when you update cell B3. So you should be used a "Worksheet_Change" procedure instead.

Try this code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim n As Long
    Dim i As Long
    Dim c As Long
    Dim dt As Date
   
'   Exit if cell B3 not updated
    If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub
   
'   Check to see that value in B3 is a date
    If Not IsDate(Range("B3")) Then
        MsgBox "Value in B3 is not a valid date"
        Exit Sub
    Else
        dt = Range("B3").Value
    End If
   
'   Count the number of worksheets in the workbook
    n = Worksheets.Count
   
'   Loop through all sheets except the first and rename then
    For i = 2 To n
        Sheets(i).Name = Format(dt, "dd-mmm")
'       Increment date by 7 days for next sheet
        dt = dt + 7
    Next i

End Sub

Joe4 - thanks so much for this! It's very nearly perfect...!

With regards to yourevent procedure question, it's because I have no idea what I'm doing haha!

For the code you provided, in which sheet code should I be pasting this? When I paste it into Sheet 1 it does exactly what I need but it starts it from Sheet2 (i.e. calls Sheet2 "1st Jan" rather than starting at Sheet1. Likewise, if I try to have that in as well as my previous code (with your changes applied) it appears it can't execute both?

On a separate note, with the code you've provided, is there a way I can dictate how many of the subsequent tabs it renames? I will want a few admin tabs at the end of the sheets to not be renamed with this macro.

Thanks so much! I feel so close!!

 

[Joe4]

You put this code in whatever sheet module you want it to run from (whatever sheet contains the B3 cell that you want to trigger it).

If you want it to update the sheet that the B3 you are updating is on, then change this line:

   For i = 2 To n

to this:

   For i = 1 To n

If there are two sheets at the end that you want to skip, then change it again like this:

   For i = 1 To (n - 2)

(hopefully you see the pattern).

 

[mushkapush]

You put this code in whatever sheet module you want it to run from (whatever sheet contains the B3 cell that you want to trigger it).

If you want it to update the sheet that the B3 you are updating is on, then change this line:

   For i = 2 To n

to this:

   For i = 1 To n

If there are two sheets at the end that you want to skip, then change it again like this:

   For i = 1 To (n - 2)

(hopefully you see the pattern).

Oh my goodness!! It's worked!!

Thankyou so much - you are an absolute lifesaver!!

 

[Joe4]

You are welcome!

Glad I was able to help.

 

[EdKnight]

This code works for me as well. I changed it for column A and sheet 2 changes with the value of A1 on sheet 1 then sheet 3 changes with the value of sheet 1 A3 then sheet 4 changes with the value of sheet 1 A5. I would like it so it sheet 3 changes with the value of sheet 1 A2 and sheet 4 changes with the value of sheet 1 A3, so it changes with 1 cell increments not 2.

How can I do this?

 

[Joe4]

This code works for me as well. I changed it for column A and sheet 2 changes with the value of A1 on sheet 1 then sheet 3 changes with the value of sheet 1 A3 then sheet 4 changes with the value of sheet 1 A5. I would like it so it sheet 3 changes with the value of sheet 1 A2 and sheet 4 changes with the value of sheet 1 A3, so it changes with 1 cell increments not 2.

How can I do this?

Your question is a bit different from the original, and you would probably do much better to post it to its own, new thread so it appears on the "Unanswered threads" list for all to see (you can include links to other threads, if you think that may be helpful). It has a much better chance of getting looks and responses.

Be sure to post the code you are using, and a sample of what your data looks like.