VBA New sheet and copy template

Tinsmith Posts 1 Registration date Thursday April 8, 2021 Status Member Last seen April 8, 2021 - Updated on Apr 9, 2021 at 05:43 AM
vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 - Apr 10, 2021 at 02:32 AM

Im struggling abit.

I want to make a button where I add a new sheet with a specific name. In this example, "App1".
But I also want to copy a templatesheet to "App1".

So when I run this it should make a sheet named App1 with the template.

Im just missing the copy template line here,

If I simply run
Sheets("Template").Copy After:=Sheets(Sheets.Count)
it adds yet another Sheet, which it shouldnt.

Any ideas?

Sub Add()
Dim i As Long, wsName As String, temp As String
Worksheets.Add After:=Worksheets(Worksheets.Count)
wsName = "App1"

If WorksheetExists(wsName) Then

    temp = Left(wsName, 3)
    i = 1
    wsName = temp & i
    Do While WorksheetExists(wsName)
        i = i + 1
        wsName = temp & i

End If
ActiveSheet.Name = wsName

End Sub

Function WorksheetExists(wsName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(wsName).Name = wsName
On Error GoTo 0
End Function

1 response

vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
Apr 10, 2021 at 02:32 AM
Hello Tinsmith,

Perhaps the following code(s) may help:-

Sub Test()

      Dim wsT As Worksheet: Set wsT = Sheets("Template")

Application.ScreenUpdating = False

      If Not Evaluate("ISREF('" & "App" & "'!A1)") Then
             wsT.Copy After:=Sheets(Sheets.Count)
             ActiveSheet.Name = "App" & Sheets.Count - 1
      End If

Application.ScreenUpdating = True

End Sub

Sub HideShapes()

        Dim ws As Worksheet
        Dim myshape As Shape
        For Each ws In Worksheets
             If ws.Name <> "Template" Then
                    For Each myshape In ws.Shapes
                        myshape.Visible = False
             End If

End Sub

You'll see that there are two codes with the main code (Test) calling the second code (HideShapes). The second code simply ensures that the button used to execute the code(s) is not visible on any of the newly created sheets.

I've attached a link to a mock-up workbook which shows you how this could work for you. Just click on the "TEST" ME button.


Please test it in a copy of your actual workbook.

I hope that this helps.