VBA code to copy sheets according to named range

Trille

New Member
Joined
Apr 26, 2010
Messages
25
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Trille

New Member
Joined
Apr 26, 2010
Messages
25
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!
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,139
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 26, 2010
Messages
25
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

New Member
Joined
Apr 26, 2010
Messages
25
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,139
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,646
Messages
5,654,567
Members
418,142
Latest member
peterappiahkubi

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