Excel: Copy a sheet multiple times and rename it

Hi James

I will take the liberty to change the "Title" of the thread in order to add clarity to your requirements. I hope you don't mind

To answer your question:

I'm afraid your request can only be achieved via VBA macros.

I f you are ok with it, then try the following steps

1) Save your workbook as an Excel Macro-Enabled Workbook (.xlsm)

2) Change the name of the sheet tab you want to copy to "Template"

3) Paste the following code on a regular module on your VBA panel

Sub CreateMonthlySheets()

Dim k As Integer

Dim shName As String

''' Unhide template

Sheets("Template").Visible = xlSheetVisible

For k = 1 To 12

    ''' shName variable creates the the month's list for the current year

    shName = Format(DateSerial(Year(Date), k, 1), "mmmm yyyy")

    Sheets("Template").Copy After:=Sheets(Sheets.count)

    On Error Resume Next

    ActiveSheet.Name = shName

    On Error GoTo 0

Next k

''' Hide template

Sheets("Template").Visible = xlSheetHidden

MsgBox "Job Done"

End Sub

The above code will do the following

a) Unhide the "Template" sheet (if hidden)

b) Make a copy of the "Template" sheet and name it after each month of the calendar year

with the following name format "mmmm yyyy",  i.e. January 2021, February 2021,...

c) Hide the "Template" sheet

d) Notify when it finished

The video below will guide you on how to access and insert a module on the VBA panel 

Do let me know if you need more help

Regards

Jeovany

https://www.youtube.com/watch?v=IIuKCxPlaTY

Hi James

I hope you are OK

I'm writing to follow up on this thread because we haven't received any news from you in days

Did you check the above-suggested solution?

May I know if you need further help?

Did you solve your problem?

Any updates will be welcome.

Regards

Jeovany

Thank you very much for your time  it worked