Create copies of Worksheet (Master) based on a Range in another worksheet

Hello Experts

A very happy New Year 2020 to all of you.

I am creating a new MIS for my team of and need your help on it.

I am using the below code to create the copies of a worksheet named "Master" around 20+ worksheets each month. I have a range of data in another Worksheet named "List of User" (Team members names) in column "K" say "K1:K25".

The below code gives me any number of copies as I wish but, I need the new sheets to be named as per the Range in "List of User" Column "K" and it become dynamic so, that whenever a new name gets added to this list I need not worry while creating copies from the "Master" worksheet.

Code:

---

Public Sub DuplicateSheetMultipleTimes()
    Dim n As Integer
    On Error Resume Next
    n = InputBox("How many copies of the active sheet do you want to make?")
 
    If n >= 1 Then
        For numtimes = 1 To n
            ActiveSheet.Copy After:=ActiveWorkbook.Sheets(Worksheets.Count)
        Next
    End If
End Sub

---

Thanks all in Advance.

Hello Sumit
Best wishes for the New Year to you too.

I think I can see approximately what it is you want. But it would be a lot clearer if you could maybe give an example.

Perhaps you can give us two workbooks.
_ One should show us the situation Before the macro is run,
and
_ the other, the After, should look like what you want after the macro is run. In other words, you do manually to the After what you want the macro to do.

Keep the data to the minimum that you need to demonstrate all possible scenarios. Desensitize any personal data. In other words make up your data, or change names etc. But the data should have a similar format and similar characteristics to real data.

And tell us exactly what you did to get the After in its final form



Alan

Thanks for your reply.

I have attached a copy of the workbook that I am working with.

Sheet "List of User" have the list of names that the macro should create new worksheets for. I have assign the vba code in "Sheet1 (Master).

When somebody click "Ctrl+shift+Q" in the "Master" worksheet the input box appears asking for how many sheets to be created. I was trying to see if the macro can create only that much of named worksheets as in the "List of User" sheet have.

e.g. I have 22 names in the "List of User" sheet so the macro should create named sheets (22 in number) name of the sheets shout be the Range "A" in "List of User".

Thanks
Again

Hi

Try this

Code:

---

Option Explicit
Public Sub DuplicateSheetMultipleTimes()
Dim wsUsrs As Worksheet: Set wsUsrs = ThisWorkbook.Worksheets("List of User")
Dim wsMstr As Worksheet: Set wsMstr = ThisWorkbook.Worksheets("Master")
Dim rngStr As Range, rngUsrs As Range
 Set rngUsrs = wsUsrs.Range("A1:A" & wsUsrs.Cells.Item(wsUsrs.Rows.Count, 1).End(xlUp).Row & "")
   
    For Each rngStr In rngUsrs
    wsMstr.Copy After:=ThisWorkbook.Worksheets.Item(ThisWorkbook.Worksheets.Count)
    Let ActiveSheet.Name = rngStr.Value
    Next rngStr
   
   





'    Dim n As Integer
'    On Error Resume Next
'    n = InputBox("How many copies of the active sheet do you want to make?")
'
'    If n >= 1 Then
'        For numtimes = 1 To n
'            ActiveSheet.Copy After:=ActiveWorkbook.Sheets(Worksheets.Count)
'        Next
'    End If
End Sub

---

Thanks a lot Alan.

It's working just fine (**)