VBA: Copy different sheet in same workbook multiple times, naming according to list in cells

[RichCowell]

Hi all,

I've found a VBA script to duplicate the current active worksheet, but I need to copy a different worksheet in the workbook X amount of times (X = number in a cell).

e.g.
Active Worksheet Name: Start
Worksheet to be copied: Master
Copied X times where X = Start!A10
Names of worksheets: Start!B10:B25

Can anyone help me out? I had assumed it would need to be done in two stages - replicating the Master sheet, then renaming them - but I was going to (try to) cross that bridge when I got the first bit done.

Thanks,

Rick

 

[My Aswer Is This]

Try this to solve the hidden sheet issue:

Sub Copy_Master()
'Modified  7/22/2019  7:33:00 AM  EDT
On Error GoTo M
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Sheets("Start").Cells(Rows.Count, "B").End(xlUp).Row
Sheets("Master").Visible = True
For i = 1 To Lastrow
    Sheets("Master").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = Sheets("Start").Cells(i, "B").Value
Next
Application.ScreenUpdating = True
Sheets("Master").Visible = False
Exit Sub
M:
MsgBox "That sheet name may already be used or you made some mistake"
End Sub

 

[My Aswer Is This]

If you get a message saying there was a error.
You may be using a duplicate name
No two sheets can have the same name
Or you may be trying to name a sheet a improper name.
Excel does not like some characters in a sheet name.

Like it does not like:


7/7/2019

It will accept
7-7-2019

And there may be more characters it does not like.

 

[RichCowell]

Thanks! Works perfectly again... It's only going to be forenames in the list so shouldn't ever include incomatible characters

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Thanks! Works perfectly again... It's only going to be forenames in the list so shouldn't ever include incomatible characters