VBA New sheet and copy template Closed

Question

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

vcoolio · Answer

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.

VBA New sheet and copy template

1 response

Similar discussions

Subscribe To Our Newsletter!