VBA sheet copy and rename

BoltE

New Member
Joined
May 4, 2013
Messages
8
I'm trying to run a macro to create (copy a template) a sheet, and then name it according to data in a table.

Here's my code:


Sub CreateSheetsFromInfo()
Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("Info").Range("D2")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

For Each MyCell In MyRange
Sheets(4).Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = MyCell.Value
Next MyCell
End Sub

It works as needed, but only on first use. If new info is added to the range, it will not add new sheets for them, as it tries to create them all again and discovers duplicate names. How can I get it to skip past existing sheets?

Also, I would like for the template sheet to be hidden, but the new sheets to be shown. If I currently hide the template, it (obviously) hides the copies, but also has trouble renaming them appropriately.

Thanks!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
BoltE

Perhaps try this....
Code:
Sub CreateSheetsFromInfo()
Dim MyCell As Range, MyRange As Range
Application.ScreenUpdating = Falsse
Application.DisplayAlerts = False
Sheets(4).Visible = True
Set MyRange = Sheets("Info").Range("D2")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
On Error GoTo Skip
For Each MyCell In MyRange
Sheets(4).Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = MyCell.Value
GoTo Good
Skip:
Sheets(Sheets.Count).Delete
Resume Next
Good:
Next MyCell
Sheets(4).Visible = False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
On Error GoTo 0
End Sub

Hope that helps.
 
Upvote 0
Thanks! Works like a champ! For other readers, there's a typo in line 3 of the suggested code, Falsse, should read False
 
Upvote 0
Do you suppose you could help me with one more aspect? I am trying to put the sheet name in a cell in each sheet. I thought I could get away doing this by simply using a cell formula, and that works, but it needs constant refreshing when switching between sheets.

So, I need to add to the VBA code to put the same name in a cell, in this case, H2.

Thanks!
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top