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]

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]

This will check to see if the sheet already exists & ignore it if so.

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

 

[Trille]

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]

You're welcome & thanks for the feedback.

 

[Trille]

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):

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:

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]

Move the Call hide7rows before the End If line.

 

[Trille]

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]

Are you unhiding the same rows on every sheet created?

 

[Trille]

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?