I have a spreadsheet I use to track my time. Each tab is a pay period and I would like for the tab name to automatically change based on the cell containing the last day of the pay period, in my case F1. RickXL was kind enough to respond to an earlier post with the following code but I can't get it to work. I'm sure it is operator error since what I know about Excel would fit in a thimble.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F1")) Is Nothing Then
On Error GoTo quit
Me.Name = Format(Range("F1").Value, Range("F1").NumberFormat)
On Error GoTo 0
End If
Exit Sub
quit:
MsgBox "Sheet name: " & Range("F1").Text & " is not valid."
End Sub
I think I am pasting it in the correct location. I went into VBA (Alt +F11), dbl clicked on Sheet 1 which opened a screen, Sheet1 (Code), and I pasted the code. I hit Run and I get a box that says Macro Name (which is empty) I put in tabname and hit Create. Now I have a screen that says Module1 (Code) Sub tabname() End Sub. What goes in Module1?
Pat
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F1")) Is Nothing Then
On Error GoTo quit
Me.Name = Format(Range("F1").Value, Range("F1").NumberFormat)
On Error GoTo 0
End If
Exit Sub
quit:
MsgBox "Sheet name: " & Range("F1").Text & " is not valid."
End Sub
I think I am pasting it in the correct location. I went into VBA (Alt +F11), dbl clicked on Sheet 1 which opened a screen, Sheet1 (Code), and I pasted the code. I hit Run and I get a box that says Macro Name (which is empty) I put in tabname and hit Create. Now I have a screen that says Module1 (Code) Sub tabname() End Sub. What goes in Module1?
Pat
Last edited: