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

Question

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

vcoolio · Accepted Answer

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

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.

I hope that this helps.

Cheerio,
vcoolio.

TrowaD · Answer

Hi Hpence,

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.

Or just use Vcoolio's code :).

Best regards,
Trowa

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

2 responses

Similar discussions

Subscribe To Our Newsletter!