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

Question

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

Report

Hpence - Updated on Dec 31, 2020 at 07:22 AM
TrowaD

Posts: 2696
Registration date: Sunday September 12, 2010
Status: Moderator
Last seen: January 28, 2021

- Jan 7, 2021 at 11:58 AM

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

Reply Ask a question

vcoolio · Answer 1 · 2020-12-31T08:08:42-0500

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 2 · 2021-01-07T11:58:44-0500

Answer 2 / 2

TrowaD

Posts: 2696
Registration date: Sunday September 12, 2010
Status: Moderator
Last seen: January 28, 2021

457
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

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

2 replies

Your reply

Subscribe To Our Newsletter!