Worksheet from a template using a master list but protecting existing worksheets

Alfalfa166

New Member
Joined
Jan 23, 2024
Messages
1
Office Version
  1. 365
Platform
  1. MacOS
Hi, with help from various threads on here I have managed to create a master list from which I can create worksheets using a template. One each created spreadsheet, I am manually filling in some information which is not present on the Master List. It would be too much information to place on the Master List and copy and paste works.

All works as I want with one exception.

When I add a new row to the master list and run the Macro, all of the manually inputted data disappears. I am running @DanteAmor code from this old thread where all worksheets are deleted and recreated

With Sheets("Master List")
For Each c In .Range("A3", .Range("A" & Rows.Count).End(3))
On Error Resume Next: Sheets(c.Text).Delete: On Error GoTo 0
Sheets("Template").Copy after:=Sheets(Sheets.Count)
Set sh = ActiveSheet
sh.Name = c.Value

Macro to create new worksheet from a template, using data from a master list.

So I am looking for a way to protect all cells in the created worksheets so that when i add a new row to the Master and run the Macro, it adds the contents of this row to a new worksheet at the end of my existing tabs without harming what has been done before. Here is my specific code:

Sub DOCUMENT_ISSUE_TRACKER()
Dim sh As Worksheet
Dim c As Range

Application.ScreenUpdating = False
Application.DisplayAlerts = False

With Sheets("Master List")
For Each c In .Range("A3", .Range("A" & Rows.Count).End(3))
On Error Resume Next: Sheets(c.Text).Delete: On Error GoTo 0
Sheets("Template").Copy after:=Sheets(Sheets.Count)
Set sh = ActiveSheet
sh.Name = c.Value
sh.Range("E2").Value = .Range("A" & c.Row).Value
sh.Range("B4").Value = .Range("C" & c.Row).Value
sh.Range("B5").Value = .Range("D" & c.Row).Value
sh.Range("B6").Value = .Range("E" & c.Row).Value
sh.Range("B7").Value = .Range("F" & c.Row).Value
sh.Range("D7").Value = .Range("G" & c.Row).Value
sh.Range("E7").Value = .Range("H" & c.Row).Value
sh.Range("B9").Value = .Range("I" & c.Row).Value
sh.Range("D9").Value = .Range("J" & c.Row).Value
sh.Range("B10").Value = .Range("K" & c.Row).Value
sh.Range("D10").Value = .Range("L" & c.Row).Value
sh.Range("B11").Value = .Range("M" & c.Row).Value
sh.Range("D11").Value = .Range("N" & c.Row).Value
sh.Range("B12").Value = .Range("O" & c.Row).Value

Next
End With

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

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
Back
Top