G
Guest
Hi,
I have my code all set to name worksheets from names in Column D (D15 to
D144). Now, rather than using only the values (employee names) in Column D
(user inputted ad hoc), I need to name the worksheets using the concatenated
value of the name in Column D and a number in Column E. For example, a user
enters "Smith, Fred" in D15 and "123456" in E15. I need a macro to name the
corresponding worksheet "Smith, Fred (123456)" and not just "Smith, Fred".
I've had some amazing help already by Tom Ogilvy, Bob Phillips, Jim
Thomlinson and Dave Peterson resulting in the following code:
Private Sub CommandButton3_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim ws As Worksheet
Set ws = ActiveSheet
Set LastCell = ws.Cells(Rows.Count, "d").End(xlUp)
Set Rng = ws.Range("d15", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Set ws = Nothing
On Error Resume Next
Set ws = Worksheets(cell.Value)
On Error GoTo 0
If ws Is Nothing Then
Sheets("Master").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
cell.Hyperlinks.Add Anchor:=cell, _
Address:="", _
SubAddress:="'" & ActiveSheet.Name & "'!A1", _
TextToDisplay:=cell.Value
End If
End If
Next
Application.Goto Reference:="Summary"
End Sub
Thanks in advance!
I have my code all set to name worksheets from names in Column D (D15 to
D144). Now, rather than using only the values (employee names) in Column D
(user inputted ad hoc), I need to name the worksheets using the concatenated
value of the name in Column D and a number in Column E. For example, a user
enters "Smith, Fred" in D15 and "123456" in E15. I need a macro to name the
corresponding worksheet "Smith, Fred (123456)" and not just "Smith, Fred".
I've had some amazing help already by Tom Ogilvy, Bob Phillips, Jim
Thomlinson and Dave Peterson resulting in the following code:
Private Sub CommandButton3_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim ws As Worksheet
Set ws = ActiveSheet
Set LastCell = ws.Cells(Rows.Count, "d").End(xlUp)
Set Rng = ws.Range("d15", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Set ws = Nothing
On Error Resume Next
Set ws = Worksheets(cell.Value)
On Error GoTo 0
If ws Is Nothing Then
Sheets("Master").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
cell.Hyperlinks.Add Anchor:=cell, _
Address:="", _
SubAddress:="'" & ActiveSheet.Name & "'!A1", _
TextToDisplay:=cell.Value
End If
End If
Next
Application.Goto Reference:="Summary"
End Sub
Thanks in advance!