Alfalfa166
New Member
- Joined
- Jan 23, 2024
- Messages
- 1
- Office Version
- 365
- Platform
- 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
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