VBA New sheet and copy template

Report
Posts
1
Registration date
Thursday April 8, 2021
Status
Member
Last seen
April 8, 2021
-
Posts
1288
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
April 24, 2021
-
Hello,

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
    Loop

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 reply

Posts
1288
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
April 24, 2021
225
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
      
wsT.Select
HideShapes

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
                    Next
             End If
        Next

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.

https://wetransfer.com/downloads/232b0acb632861f91c6c5c8dea1249ce20210410062934/52a27b

Please test it in a copy of your actual workbook.

I hope that this helps.

Cheerio,
vcoolio.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!