My below code does a few things, asks for a name of a new sheet, creates it and enters the name into the next blank C cell.
Cells next to this, D,E,F, and G have relevent data that i call from the new sheets when they are created EG: Project manager, start date etc.
Rather than having these all populated and having an ugly table with "#Ref!" everywhere I thought it would be best to do this whilst a new project is being created.
Basically, depending on the "EMRange" used below, for example the next one empty one being C6, I'd then like D6,E6,F6 and G6 to auto fill the formula from the cells above.
I gather I need to use selection.autofill but I'm unsure on how to get the destination to be those specific cells depending on what the EMRange was in the part of the script previous.
Public Sub CopySheetAndRenameByCell()
Dim newName As String, Emrange As Range, wsNew As Worksheet, wb As Workbook
Dim wsIndex As Worksheet
newName = InputBox("Enter the name of the new project", _
"Copy worksheet", ActiveCell.Value)
If newName <> "" Then
Set wb = ThisWorkbook
wb.Worksheets("Project Sheet BLANK").Copy _
After:=wb.Worksheets(wb.Worksheets.Count)
Set wsNew = wb.Worksheets(wb.Worksheets.Count)
On Error Resume Next 'ignore error on rename
wsNew.Name = newName
On Error GoTo 0 'stop ignoring errors
Set wsIndex = wb.Worksheets("Client Projects Overview")
Set Emrange = wsIndex.Range("C" & Rows.Count).End(xlUp).Offset(1)
wsIndex.Hyperlinks.Add Anchor:=Emrange, _
Address:="", SubAddress:="'" & wsNew.Name & "'!A1", _
TextToDisplay:=wsNew.Name
'reset font style
Emrange.Font.Underline = xlUnderlineStyleNone
Emrange.Font.ColorIndex = xlAutomatic
Emrange.Font.Name = "Century Gothic"
Emrange.Font.Size = "10"
If wsNew.Name <> newName Then 'in case sheet could not be renamed....
MsgBox "Name provided '" & newName & _
"' is not valid as a worksheet name!", vbExclamation
End If
End If
End Sub
