I know that I'm missing something simple and am hoping that someone can point out what I'm missing ![Smile :) :)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
Here goes:
I have an 8 tab spreadsheet (Excel 2013)
Formulas in Sheet1 151:A157 update with DAY()+1 based on the date entered into Sheet1 A1.
So with 9/8/18 entered into A1, A151=9; A152=10; A153=11 and so on.
I want my code to rename Sheet2 thru Sheet8 based on the contents of A151:A157 when I change the date in A1.
Example Sheet1 A1 = 9/8/18
Sheet1 A151 = 9 then rename Sheet2 to 9
Sheet1 A152 = 10 then rename Sheet3 to 10
Sheet1 A153 = 11 then rename Sheet4 to 11
and so on.
The code below works if I enter a value into A151:A157 directly, but not automatically from formulas when they change based off of new date entered into A1.
Any help would be greatly appreciated. Thanks!
Here goes:
I have an 8 tab spreadsheet (Excel 2013)
Formulas in Sheet1 151:A157 update with DAY()+1 based on the date entered into Sheet1 A1.
So with 9/8/18 entered into A1, A151=9; A152=10; A153=11 and so on.
I want my code to rename Sheet2 thru Sheet8 based on the contents of A151:A157 when I change the date in A1.
Example Sheet1 A1 = 9/8/18
Sheet1 A151 = 9 then rename Sheet2 to 9
Sheet1 A152 = 10 then rename Sheet3 to 10
Sheet1 A153 = 11 then rename Sheet4 to 11
and so on.
The code below works if I enter a value into A151:A157 directly, but not automatically from formulas when they change based off of new date entered into A1.
Any help would be greatly appreciated. Thanks!
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cells As Range
On Error Resume Next
For Each cell In Target
If cell.Address = "$A$151" Then
Sheets(2).Name = cell.Text
ElseIf cell.Address = "$A$152" Then
Sheets(3).Name = cell.Text
ElseIf cell.Address = "$A$153" Then
Sheets(4).Name = cell.Text
ElseIf cell.Address = "$A$154" Then
Sheets(5).Name = cell.Text
ElseIf cell.Address = "$A$155" Then
Sheets(6).Name = cell.Text
ElseIf cell.Address = "$A$156" Then
Sheets(7).Name = cell.Text
ElseIf cell.Address = "$A$157" Then
Sheets(8).Name = cell.Text
End If
Next cell
If Err > 0 Then MsgBox "Sheet named renamed due to name conflict. Please try again."
End Sub
Last edited by a moderator: