Copying a template sheet's content to a series of newly created sheet tabs.

**ZootlyData** · 02-16-2016, 04:51 PM

I would like to copy a template sheet's contents(basically the entire sheet) to each newly created tab. The code below creates each new sheet tab and takes the values of a column range of companies on a separate sheet in the same workbook to name each new sheet tab. Where I am running into an issue is trying to get the actual "Template sheet"(within the same workbook) and it's contents copied to each one of these newly created tabs. This is the code I have so far.

'Select Company names
Sub AddSheets()
Dim cell As Excel.Range
Dim wbToAddSheetsTo As Excel.Workbook

Set wbToAddSheetsTo = ActiveWorkbook
For Each cell In Selection
    With wbToAddSheetsTo
        .Sheets.Add After:=.Sheets(.Sheets.Count)
        On Error Resume Next
        ActiveSheet.Name = cell.Value
        If Err.Number = 1004 Then
          Debug.Print cell.Value & " already used as a sheet name"
        End If
        On Error GoTo 0
    End With
Next cell
End Sub

I would appreciate any suggestions that would copy that "Template Sheet" to each of the new tabs as the tab itself is being created.

**Richard Buttrey** · 02-16-2016, 05:18 PM

Hi,

I think I'd simplify this to the following. First create a range name "SheetNames" that covers your list of sheet names

Sub CopyTemplate()
    Dim rCell As Range
    For Each rCell In Range("SheetNames")
        If rCell.Cells(1, 2) = "" Then
            Sheets("Template").Copy After:=Sheets(Sheets.Count)
            ActiveSheet.Name = rCell.Value
            rCell.Cells(1, 2) = "Y"
        End If
    Next rCell
End Sub

**ZootlyData** · 02-16-2016, 05:42 PM

Thanks for the quick response Richard. I seem to be getting an error though. not sure why. I am selecting/highlighting the cells in the column that I would like named tabs to be created for but when I click the button I mapped this macro to, the error appears.

I have included the example sheet with this reply so you can have a better idea of how the data is formatted. I appreciate your help.

**Richard Buttrey** · 02-16-2016, 07:17 PM

Hi,

You haven't named the I2:I6 range of cells on the Summary sheet as "SheetNames" which is what the macro is expecting.
Neither have you changed the template sheet name in the macro. Your tab name is "Revenue Template". Your original hadn't actually said what it was called so I assumed it was just called "Template"

However now I've seen your workbook I know the VBA code name for the "Revenue Template" sheet is Sheet2. Therefore it's much better to use the VB code name because if you ever change the tab name and attempt to run the macro you'd get an error. Hence use

Sub CopyTemplate()
    Dim rCell As Range
    For Each rCell In Range("SheetNames")
        If rCell.Cells(1, 2) = "" Then
            Sheet2.Copy After:=Sheets(Sheets.Count)
            ActiveSheet.Name = rCell.Value
            rCell.Cells(1, 2) = "Y"
        End If
    Next rCell
End Sub

**ZootlyData** · 02-16-2016, 07:27 PM

Hey Richard,

Thanks again, but I am still getting the same error. I have attached an updated excel sheet that has your updated code included. Any ideas? Thanks again.

**Richard Buttrey** · 02-17-2016, 04:01 AM

Hi,

You still haven't created the range name "SheetNames". See my first comment in my last post.
Currently this should cover I2:I6 on the Summary.

If you periodically add new rows to the summary then it would be advisable to make this range a dynamic range name so that it automatically adjusts. If so then define it thus:

Formula:

=OFFSET('Summary'!$J$2,0,0,COUNTA('Summary'!$J:$J)-1,1)

Copying a template sheet's content to a series of newly created sheet tabs.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Copying a template sheet's content to a series of newly created sheet tabs.

Re: Copying a template sheet's content to a series of newly created sheet tabs.

Re: Copying a template sheet's content to a series of newly created sheet tabs.

Re: Copying a template sheet's content to a series of newly created sheet tabs.

Re: Copying a template sheet's content to a series of newly created sheet tabs.

Re: Copying a template sheet's content to a series of newly created sheet tabs.

Thread Information

Users Browsing this Thread

Similar Threads

hyperlink of newly created sheet

[SOLVED] Worksheet Activate when user selects sheet NOT when sheet is created from a template sheet

[SOLVED] Pulling data from a main sheet and placing it onto a newly created sheet

Getting Macro to reference newly created sheet

Updating Summary Sheet from Newly Created Sheet

How to copy to newly created sheet and save as PDF?

Automatically transferring data from one sheet to newly created sheet in a workbook

Bookmarks

Bookmarks

Posting Permissions