Reproduce same sheet multiple times

[austin350s10]

Is there a way to program a single button so when clicked it takes a very hidden sheet, copies it and turns it into a new sheet? Only catch is it would need to change the sheet name each time it is clicked.

EXAMPLE WORKBOOK STRUCTURE:
(fresh workbook no button click)
MainPage(unhidden), FirstReport(unhidden), ReportTemplate(very hidden)

(first button click)
MainPage(unhidden), FirstReport(unhidden), Report-[CurrentDate](unhidden), ReportTemplate(very hidden)

(second button click)
MainPage(unhidden), FirstReport(unhidden), Report-[CurrentDate(unhidden), Report-[CurrentDate](unhidden), , ReportTemplate(very hidden)

 

[venkat1926]

first prepare a macro and assign this macro to the button

the macro will be something like this (modify it to suit you)

Sub test()
Dim x As String
Application.ScreenUpdating = False
x = InputBox("type the name you want to give to the new sheet for e.g. austin1    ")
Sheets("Sheet1").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = x
Application.ScreenUpdating = True
End Sub

 

[btadams]

assign this to a button in your main sheet:

Sub CopyTemplate()
Dim lngCalc As Long

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        lngCalc = .Calculation
        .Calculation = xlCalculationManual
    End With
    
    Sheet2.Visible = xlSheetVisible
    Sheet2.Copy after:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Report - " & Format(Now(), "mm-dd-yy")
    Sheet2.Visible = xlSheetVeryHidden
    
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = lngCalc
    End With
End Sub

NOTE: this assumes the veryhidden sheet has the object name "Sheet2". This will need to be changed based on your template sheet object name.