Copying Worksheet 20 times then Rename each Worksheet

[Kacer26]

Hello!

I am trying to copy a worksheet about 20 times and then I need to rename each worksheet by a string name.

For Example:

ActiveSheet.Name = "VOTD Non-EXW"
ActiveSheet.Name = "VOTD Non-Pharma"
ActiveSheet.Name = "VOTD Pharma"


I found the following code that renames the worksheet by a specified date. I have been trying to adapt the code to rename my activesheet by a string but I can't seem to figure it out.

So...I managed to find this code:

Sub MakeYear()
Dim D As Date, L As Long
For L = 1 To 20
D = DateSerial(2004, 1, 1 + (L - 1) * 7)
Worksheets(1).Copy _
After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(D, "m-d-yy")
Next
End Sub

Any Assistance would be greatly appreciated

 

[VoG]

Do you have the names of the worksheets stored somewhere?

 

[Kacer26]

Do you have the names of the worksheets stored somewhere?

I do have a table in another worksheet (same workbook) with the specified names of each worksheet.

Is that the only way the renaming of the worksheets can be done?

 

[VoG]

Well, unless you want random names then you''ll have to have as list or hardcode the list in the macro. What is the name of the sheet and what range holds the worksheet names?

 

[Kacer26]

The name of the Worksheet with the Worksheet Names
is: WorkSheetReference

And the Range is A1:A20

 

[VoG]

Try this

Sub copysheets()
Dim i As Long
For i = 1 To 20
    Sheet1.Copy after:=Worksheets.Count
    ActiveSheet.Name = Sheets("Reference").Range("A" & i).Value
Next i
End Sub