Copy Sheet 2 and create and rename multiple sheets based on the list in Sheet 1 Column A

Friends, I need help with the below.

How do I run my code automatically on Opening the file...I wrote the macro to copy Sheet "Template" and create multiple sheets based on a Column list ("HM") of values in sheet "Data"....the macro runs within the code but does not run when I open the file even with the Private Sub Workbook_Open() command. I have my list in the "Data" sheet column "HM" and the sheet to be copied and created is the "Template". I inserted a button to run it but want to run it automatically on opening. Also the button click only works when I place the Button in the Data Sheet. I want the button to be placed in the Template Sheet. Please help. First priority is to make it run automatically on opening

Here is my code created under Module1:

Sub NewSheets()

Dim i As Integer
Dim ws As Worksheet
Dim sh As Worksheet
Set ws = Sheets("Template")
Set sh = Sheets("Data")
Application.ScreenUpdating = 0

For i = 5 To Range("HM" & Rows.Count).End(xlUp).Row
Sheets("Template").Copy After:=sh
ActiveSheet.Name = sh.Range("HM" & i).Value

Next i
End Sub

And at the workbook level I used the below code:

Private Sub Workbook_Open()
NewSheets
End Sub
Show less

Try This

Please Login or Register  to view this content.

Sowy ...Should be this...Put it in a Module. Why don't you upload your file so that we can have a look.

Please Login or Register  to view this content.

Hi abyk

Here you go. i deleted your command button on sheet. On open, your code will run automatically.
Code in two different modules.

Pleasure... Thanks for the rep points