Add a worksheet with the name of a selected cell (which changes)

[Griffin29]

Hi, I'm struggling to work this out despite me thinking it really cannot be hard (still very much a newbie).

I want to select a cell in an existing worksheet then run a macro that inserts a new sheet (at the end) and names it the contents of the active cell. An added bonus would be to Paste the same cell data into the new worksheet at A1. I've only got this far and clearly the rename doesn't work

Dim newsheet
Set newsheet = Sheets.Add(After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet)
newsheet.Name = ActiveCell.Value


Edit***** a really cheeky extra question - Is it possible to turn the selected cell into a hyperlink to the new sheet of the same name ? so if I select a cell with contents of "Dave", a new sheet called Dave will be made and the cell would become a link to it ?





Many thanks,

 

[AlphaFrog]

Sub Cheeky_Dave()
    With ActiveCell
        If .Value <> Empty Then
            If Not Evaluate("ISREF('" & .Value & "'!A1)") Then 'Test if worksheet name exists
                Worksheets.Add After:=Sheets(Sheets.Count)
                ActiveSheet.Name = .Value
                Range("A1").Value = .Value
                .Parent.Hyperlinks.Add Anchor:=.Cells(1), Address:="", SubAddress:="'" & .Value & "'!A1", TextToDisplay:=.Value
                .Parent.Select
            Else:
                MsgBox "A sheet named '" & .Value & "' already exists. ", vbExclamation, "Invalid Sheet Name"
            End If
        End If
    End With
End Sub

 

[Griffin29]

AlphaFrog THANK YOU THANK YOU THANK YOU - That is perfect