VBA code to copy sheets according to named range

[Trille]

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

 

[Fluff]

Unhiding the "template" sheet makes the macro behave as expected,

In that case I'm not sure what your problem is.

 

[Trille]

Sorry, I meant unhiding it permanently, removing its sub "ThisWorkbook.Sheets("Template").Visible = False". My problem is that I would like for it to be hidden until its time for it to be copied.. Could it be that the code above interferes in the loop as it is placed after "Next cell"?

Sorry for being unclear!

 

[Fluff]

I can see no reason why your code should be giving problems, as you are unhiding the sheet at the start of the code.

 

[Trille]

Ok, thanks for your input.. One last question - Could it be due to the fact that the template sheet itself contain the code "ThisWorkbook.Sheets("Template").Visible = False", meaning that it would rehide itself at every operation?

Anyway, I really appreciate your time and effort!

 

[Trille]

Solved, that was the problem! I removed the sub "ThisWorkbook.Sheets("Template").Visible = False" from the Template sheet and just set its property to xlsheethidden... Instead, I will write a sub to hide it upon deactivation.

Thanks again Fluff, hope I'll be able to repay you someday!

 

[Fluff]

Glad you sorted it & thanks for the feedback.