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
 

Some videos you may like

Excel Facts

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

Watch MrExcel Video

Forum statistics

Threads
1,123,270
Messages
5,600,635
Members
414,398
Latest member
dhune

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