Use Cell Reference as new sheet name

[micko1]

Yet another stumbling block to solve. The following code enters data into the spreadsheet in the next vacant row. Then copies a template worksheet. What I am trying to do is name the new copied worksheet after a reference created in the leadup code. Sheetname needs to be referenced from ".Cells(lRow, 3).Value = Me.staff_name.Value"
Thanks heaps for taking the time to take a look.
Mick

Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
'worksheet to copy data to
Set ws = Worksheets("Staff")
'find  first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row
 
lPart = Me.cboPart.ListIndex
 
If Trim(Me.cboPart.Value) = "" Then
  Me.cboPart.SetFocus
  MsgBox "Please enter Managers name"
  Exit Sub
End If
'copy the data to the database
With ws
  .Cells(lRow, 1).Value = Me.cboPart.Value
  .Cells(lRow, 2).Value = Me.cboLocation.Value
  [COLOR=red].Cells(lRow, 3).Value = Me.staff_name.Value[/COLOR]
  .Cells(lRow, 4).Value = Me.txtQty.Value
  .Cells(lRow, 5).Value = Me.Lic_due.Value
End With
[COLOR=red]'Not sure what to do here if this is the right spot to do it.[/COLOR]
MySheetName=.Cells(lrow, 3).value
Sheets("Staff Template").Activate
    ActiveSheet.Copy Before:=Sheets(3)
    Sheets("Staff Template (2)").Name = "MySheetName"
'clear the data
Me.cboPart.Value = ""
Me.cboLocation.Value = ""
Me.staff_name.Value = ""
Me.txtQty.Value = ""
Me.Lic_due.Value = ""
Me.cboPart.SetFocus
End Sub

 

[bertie]

Consider this line:

MySheetName=.Cells(lrow, 3).value

Which worksheet is it referring to? Try:

MySheetName=[COLOR="Red"]ws[/COLOR].Cells(lrow, 3).value

But do you really need this variable? You already have this value stored in a control, Me.staff_name.Value.

Why not use the control to name the new worksheet?

[color=green]'mysheetname = .Cells(lRow, 3).Value[/color]
Sheets("Staff Template").Copy Before:=Sheets(3)
ActiveSheet.Name = Me.staff_name.Value

 

[micko1]

Bertie
Last example works fine. I had created a workaround by calling another macro to find last row and then reference the required cell. It worked but not as well as your suggestion. Thanks for the input it is greatly appreciated.

Mick