My makro adds a worksheet but I want it to include some preset data

[Griffin29]

Hi Folks,

with some help from here (thank you again) I have been using a macro that makes a new sheet which is named the same as the highlighted cell. Works brilliantly but I now need it to add more information (it currently pastes the sheet name only).

Ideally I have another sheet set up called 'Template' and I would like to insert all the data (it is only text) into the newly created sheet. If I select the Template sheet (or a range called template ), I cant seem to get back to the newly created sheet to do the paste.

Help

Here is the code I am currently using

Public Sub Insertsheet()


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

 

[Fluff]

Why not just copy the template sheet.

Public Sub Insertsheet()
With ActiveCell
   If .Value <> Empty Then
      If Not Evaluate("ISREF('" & .Value & "'!A1)") Then 'Test if worksheet name exists
         Sheets("Template").Copy 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]

That is brilliant - thank you I couldn't for the life of me work it out and it's so much more elegant than what I was trying

 

[Fluff]

You're welcome & thanks for the feedback