Using VBA to copy and name a worksheet

I'm trying to use VBA to automatically copy a worksheet (within the same workbook) and then give that worksheet a name dictated by the contents of a cell. I have pulled together some code by searching forums etc but it just ain't working...

Code is:

Dim WsName As String
    
Sheets("Pt_Type_Query").Range("U3").Select

Do
ActiveCell.Offset(l, 0).Select

With Target
    Set WsName = ActiveCell.Value
End With
    
    Sheets("1").Select
    Sheets("1").Copy After:=Sheets("1")
        
    ActiveSheet.Name = WsName.Value

l = 1

Loop Until ActiveCell.Value = ""

The line that appears to be causing problems is highlighted in red...

Thanks in advance to anyone who can solve this!

Try changing

Sheets("Pt_Type_Query").Range("U3").Select

to

Application.GoTo Sheets("Pt_Type_Query").Range("U3")

Edit: Never mind that either ... :embarassed:

Third time's the charm?

    Dim cell    As Excel.Range

    With Sheets("Pt_Type_Query")
        For Each cell In .Range("U3", .Range("U3").End(xlDown))
            Sheets("1").Copy After:=Sheets("1")
            ActiveSheet.Name = cell.Text
        Next cell
    End With

Assuming this works, there's a bug in Excel that will cause it to fail after copying some number of sheets -- 10, 20, ...

If that's a problem, there are other options.

foxtrotdelta,

This error occurs when your code is in a module and the variables used are not defined in that same module.

This code does nothing if it is defined in Worksheet Code:

Sub float()
Dim MyName As Name
For Each MyName In Names
  Debug.Print MyName.Name
Next
End Sub

, but it does when defined in a Module

Questions: Where is your code located
Are you variables Local or Public

Try to find a solution in this direction.

Hope it helps.