cell.Value & "(" & cell.Offset(0,1).Value & ")"

this should work
susan

On Mar 12, 10:38 am, klysell .(donotspam) wrote:
Thanks Gary's Student! I was wondering how I could name the worksheet name
with the concatenated value surrounded by parenthesis. For example, "Lysell,
Kent(123456)" rather than "Lysell, Kent123456".

Thanks!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211



"Gary''s Student" wrote:
Where you use:
cell.Value
try
cell.Value & cell.Offset(0,1).Value
--
Gary''s Student
gsnu200710

"klysell" wrote:

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!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211- Hide quoted text -

- Show quoted text -