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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,137
Office Version
  1. 365
Platform
  1. Windows
You need to remove this line cell.Select Also as you are using option Explicit that code should not run as you have a typo.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,137
Office Version
  1. 365
Platform
  1. Windows
This will check to see if the sheet already exists & ignore it if so.
VBA Code:
Sub CreateNamedTemplates()
   Dim rng As Range
   Dim cell As Range
   Application.ScreenUpdating = False
   Set rng = Range("Companies")
   For Each cell In rng
      If Not Evaluate("isref('" & cell.Value & "'!A1)") Then
         Sheets("Template").Copy After:=Sheets(Sheets.Count)
         ActiveSheet.Name = cell.Value
      End If
   Next cell
   Application.ScreenUpdating = True
End Sub
 
Solution

Trille

New Member
Joined
Apr 26, 2010
Messages
25
Hi Fluff!

Thanks a lot, you made my day! I noticed that there was a typo...

Anyway, got an error du to the "ActiveSheet.Name = cell.Value", but it worked will with the original line.

Thanks a million.. again! :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,137
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You're welcome & thanks for the feedback.
 

Trille

New Member
Joined
Apr 26, 2010
Messages
25
Hi @Fluff !

Sorry for resurrecting this thread, but I have encountered an issue while trying to expand on this code.

1. Since the "Template" sheet referred to above is hidden, all created copies were also created in hidden. Obviously, I didn't want that so I added code to unhide/hide the template sheet according to the below (nevermind the arrows, they are just added to show which rows are new):

Code:
Sub CreateNamedTemplatesandscope()

Dim rng As Range

Dim cell As Range

-> ThisWorkbook.Sheets("Template").Visible = True

Application.ScreenUpdating = False

Set rng = Range("Companies")

For Each cell In rng

If Not Evaluate("isref('" & cell.Value & "'!A1)") Then

Sheets("Template").Copy After:=Sheets(Sheets.Count)

ActiveSheet.Name = cell.Value

End If

-> Call hide7rows

Next cell

Application.ScreenUpdating = True

-> ThisWorkbook.Sheets("Template").Visible = False

End Sub

2. You'll notice that I am calling another sub which basically hides selected parts of each created sheet (the template sheet has a lot of predefined units, this macro looks for an "x" related to the "Company" range and hides all that is not selected). It looks like this:
VBA Code:
Sub hide7rows()

    Dim rng As Range

    Dim lastrow As Long

    Rows.EntireRow.Hidden = False

    lastrow = Cells(Rows.Count, 3).End(xlUp).Row

    Set rng = Range("A7:A" & lastrow)

    rng.EntireRow.Hidden = True

    For Each c In rng

        If UCase(c.Value) = "X" Then

            Rows(c.Row & ":" & c.Row + 6).EntireRow.Hidden = False

        End If

    Next

End Sub

Problem number one is that excel totally crashes when there are e.g. fifteen companies for which a sheet has to be copied.

Problem number two is that when I do get a number of copied sheets that are manageable, all sheets between the first and the last are hidden and named "Template(2,3,4,N).

Would it lighten up the code if I entered the second sub directly into the first instead of calling for it? Perhaps it would also run more smoothly if I remove the loop that looks for already existing sheets? With regard to the problem of having sheets hidden, I honestly have no idea...

If I can get any direction on how to solve this, at least a direction, I would be eternally grateful!

Thanks so much in advance!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,137
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Move the Call hide7rows before the End If line.
 

Trille

New Member
Joined
Apr 26, 2010
Messages
25
Thanks for the input! I'm still getting the errors.. When manually unhiding the "Template" sheet and removing the unhide/hide command from the code, it all works as expected. Obviously, there is an interference in the loop?!

Also, do you have any suggestion regarding the macro being slow? Could it be due to the code itself or the number of macros in the template sheet that have to be copied?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,137
Office Version
  1. 365
Platform
  1. Windows
Are you unhiding the same rows on every sheet created?
 

Trille

New Member
Joined
Apr 26, 2010
Messages
25
No, basically, it works like this:
- The "Template" sheet has 30-ish predefined matrices that fetches their names from a named range (units). (hence, som of the will be empty)
- The sheet with company names from where the main code fetches the number of sheets to copy, is itself another matrix. The first column contains the companies (named range vertically) and the subsequent columns contain the transposed list of units. The user then has to enter an "x" in each of the columns that is applicable, determining the number of unit sections that should be visible/carried over from the template sheet.

The main problem is not hiding the content of each copied sheet, but rather the sheets not copying properly. Unhiding the "template" sheet makes the macro behave as expected, meaning that I get the same number of sheets as the number of companies listed, with the correct sections unhidden. However, when applying the code above with the template sheet being hidden, something weird happens... the correct number of sheets are indeed being copied, but all except the first and the last are copied as hidden with the incorrect name (each sheet is named Template(N) ).

Should there perhaps be som kind of code line that makes sure that the loops are completed before "ThisWorkbook.Sheets("Template").Visible = False", i.e. putting something in between "next cell" and that line?

Does my explanation make sense?
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,132,645
Messages
5,654,556
Members
418,140
Latest member
ahepple86

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