Hello!
New week, new challenges.. I am trying to write a VBA code that copies a template sheet as many times as there are companies in a dynamic named range and that renames each tab to fit the names in that range. The code I am using is the following:
Option Explicit
Sub CreateNamedTemplates()
Dim rng As Range
Dim cell As Range
Application.ScreenUpdating = False
Set rnge = Range("Companies")
For Each cell In rnge
cell.Select
Sheets("Template").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = cell.Value
Next cell
Application.ScreenUpdating = True
End Sub
This is returning a run-time error referring to method of range class failure. I've tried several changes but always end up with some kind of error.. Perhaps one of you can identify the issue?
My next question is that I would like to expand on this macro so that when runs, if there are any additions since the last time it was run, it only creates copies for those additions and not for the entire range (and in a perfect world also the opposite, i.e. if any companies have been removed from the range).
Anyway, if I could just get some assistance on the first issue, that would be golden!
Thanks in advance!
/Trille
New week, new challenges.. I am trying to write a VBA code that copies a template sheet as many times as there are companies in a dynamic named range and that renames each tab to fit the names in that range. The code I am using is the following:
Option Explicit
Sub CreateNamedTemplates()
Dim rng As Range
Dim cell As Range
Application.ScreenUpdating = False
Set rnge = Range("Companies")
For Each cell In rnge
cell.Select
Sheets("Template").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = cell.Value
Next cell
Application.ScreenUpdating = True
End Sub
This is returning a run-time error referring to method of range class failure. I've tried several changes but always end up with some kind of error.. Perhaps one of you can identify the issue?
My next question is that I would like to expand on this macro so that when runs, if there are any additions since the last time it was run, it only creates copies for those additions and not for the entire range (and in a perfect world also the opposite, i.e. if any companies have been removed from the range).
Anyway, if I could just get some assistance on the first issue, that would be golden!
Thanks in advance!
/Trille