Copying multiple templates

jondallimore

Board Regular
Joined
Apr 26, 2012
Messages
136
Hello,

I have the code below which copies the Template worksheet depending on a value in column A (rnglist).

Can anyone think of a way to adapt it so that I could use several template sheets?

I want to have several template sheets called Mon1, Tue1, Wed1, Thurs1, and Fri1, and the same; Mon2, Tue2 etc.

I then want the code to select which template to copy based on values in column B - which will contain eg, "Mon1", and to use the values in Column A as the name for the sheet.

If anyone can help, I would be incredibly grateful.

Many thanks
Jon

Code:
Doit:    Application.ScreenUpdating = False
    For Each cell In rnglist
        If cell.Value <> "" Then
            On Error Resume Next
            'test if worksheet exists
            If Len(Worksheets(cell.Value).name) = 0 Then
                On Error GoTo 0
                'copy worksheet named "Template"
                Worksheets("Template").Copy After:=Worksheets(Worksheets.Count)
                ActiveSheet.name = cell.Value 'name new sheet
                'Create hyperlink
                Let x = "'" & cell.Value & "'!A1"
                cell.Parent.hyperlinks.Add Anchor:=cell, _
                                           Address:="", _
                                           SubAddress:=x, _
                                           TextToDisplay:=cell.Value
       
            End If
            On Error GoTo 0
        End If
    Next cell
    CommandButton1.Parent.Activate    'go back to the source worksheet
    'Delete "Other" Sheets not on the list
    Application.DisplayAlerts = False
    For Each ws In Worksheets
        'Keep these non-list sheets
        If InStr("|Navigation|Template|Instructions|", "|" & ws.name & "|") = 0 Then
            'Test if each sheet is on the list
            If WorksheetFunction.CountIf(rnglist, ws.name) = 0 Then ws.Delete
        End If
    Next ws
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,987
Messages
5,834,720
Members
430,313
Latest member
smartykatwinks

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