Previous Topic Next Topic

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

Solved
Report
-
 rogerio -
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
Related:

2 replies

Answer 1 / 2
Posts
1325
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 17, 2021
 240
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.
Report

It works perfectly well . Thanks
Answer 2 / 2
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
 490
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
Reply Ask a question