I have created code to copy a worksheet multiple times and rename based on a list in excel. However, I am receiving an error on the ActiveSheet.Name=Sheets.... line. Please help. This is my code.
Sub CopySheet()
Dim i As Long, LastRow As Long, ws As Worksheet
Sheets("Master").Activate
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LastRow
Sheets("Master").Copy After:=Sheets(i)
ActiveSheet.Name = Sheets("Dates").Cells(i, 1)
Next i
End Sub
System Configuration: Windows / Chrome 84.0.4147.89
Related:
Copy worksheet multiple times and rename
Copy template worksheet multiple times and rename based on a list - Best answers
Excel vba copy sheet and rename based on list - Best answers
I'm assuming that the "Master" sheet is your template sheet which you are wanting to copy and the list of names to name each new sheet is in Column A of the "Dates" sheet starting in cell A1. If this is correct, then changing/adding to your code a little as follows should work for you:-
Option Explicit
Sub CopySheet()
Dim wsM As Worksheet
Dim wsNames As Range, c As Range
Set wsM = Sheets("Master")
Set wsNames = Sheets("Dates").Range("A1:A" & Rows.Count).SpecialCells(2)
Application.ScreenUpdating = False
For Each c In wsNames
If Not Evaluate("ISREF('" & c.Value & "'!A1)") Then
wsM.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = c.Value
End If
Next c
wsM.Select
Application.ScreenUpdating = True
End Sub
This code will create a new sheet based on the "Master" sheet for each unique name in Column A of the "Dates" sheet without duplication.
Please test the code in a copy of your actual workbook.
To create an understanding as to why your code produces on error.
You count the rows on your Master sheet and then use your row count on your Dates sheet. When your Master row count is higher then your dates row count, then your code tries to rename a sheet with a blank cell, which produces an error as that can't be done.
To use your own code change Master into dates on the 3rd code line.
