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!
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows
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.
 

BoltE

New Member
Joined
May 4, 2013
Messages
8
Thanks! Works like a champ! For other readers, there's a typo in line 3 of the suggested code, Falsse, should read False
 

BoltE

New Member
Joined
May 4, 2013
Messages
8
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,256
Messages
5,600,556
Members
414,388
Latest member
Pkmep4

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
Top