VBA Code to Copy Worksheet multiple times and rename based on list

Hpence - Updated on Dec 31, 2020 at 07:22 AM
 rogerio - Sep 4, 2021 at 02:03 PM

New VBA user..

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
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

2 responses

vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Dec 31, 2020 at 08:08 AM
Hello Hpence,

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

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.

I hope that this helps.

It works perfectly well . Thanks