Previous Topic Next Topic

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

Solved/Closed
Hpence - Updated on Dec 31, 2020 at 07:22 AM
 rogerio - Sep 4, 2021 at 02:03 PM
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
vcoolio
Posts
1356
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 11, 2022
 250
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

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.
2
rogerio
Sep 4, 2021 at 02:03 PM
It works perfectly well . Thanks
0
Answer 2 / 2
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
 515
Jan 7, 2021 at 11:58 AM
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
0