Is there a way to automatically add a code in a newly created worksheet?-CodePudding

I want see if there is a way to track changes in a newly created tab only. Below are two sets of code that I've come up with in my research. For reference, "Overview" is where I put all the inputs that the newly created tab will have and "Variance Template" is the template I copy for the newly created tab. Now, when I create a new tab using the macro, I also need the tracking code to be copied within that new tab. Does anyone know if this is possible?

Create a new tab code:

Sub AddNewTab()

'Assign Overview tab to be read as "ws"
Dim ws As Worksheet
Set ws = Worksheets("Overview")
 
'Assign newly created tab as "newWs" and rename it to cell D6 in Overview tab
Dim newWs As Worksheet
Set newWs = Sheets.Add(After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count))
newWs.Name = ws.Range("D6").Value

'Copy data from other, already created, comparison tab into new tab
Worksheets("Variance Template").Range("A1:A59").EntireRow.Copy
newWs.Range("A1").PasteSpecial
Worksheets("Variance Template").Range("A1:BJ59").Copy
newWs.Range("A1").PasteSpecial xlPasteColumnWidths

'Copy comparison name/month/year, last closed month/year, and service type
ws.Range("D6").Copy
newWs.Range("F2").PasteSpecial Paste:=xlPasteValues
ws.Range("Q8").Copy
newWs.Range("L2").PasteSpecial Paste:=xlPasteValues
ws.Range("Q12").Copy
newWs.Range("P2").PasteSpecial Paste:=xlPasteValues

'Hide gridlines in newly created tab
newWs.Activate
ActiveWindow.DisplayGridlines = False

'First disables any freezed panes, then freezes panes to row 9 & column 7
ActiveWindow.FreezePanes = False
Cells(9, 7).Select
ActiveWindow.FreezePanes = True

'Disables "ignore errors" notices
Application.ErrorCheckingOptions.BackgroundChecking = False

'Change zoom to 80%
ActiveWindow.Zoom = 80

'Change tab color to green
ActiveSheet.Tab.Color = RGB(146, 208, 80)

End Sub

Tracking Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:BJ200")) Is Nothing Then
        Application.EnableEvents = False
        With newWs
            .Range("F3").Value = "Last Updated: " & Environ("username") & " " & Format(Now, "MM/dd/yyyy h:mm:ss_ AM/PM")
        End With
    End If
    Application.EnableEvents = True
End Sub

I need this code to copy over every time I create a new tab using the first set of code. I'm relatively new to VBA so let me know if you need any other type of information.

Thank you.

CodePudding user response：

This worked for me:

Sub AddNewTab()
    
    Dim ws As Worksheet, wb As Workbook
    Dim newWs As Worksheet
    
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Overview")
     
    ' Variance Template is a hidden sheet (first tab position)
    '    zoom, freeze panes etc are already set up on this sheet
    wb.Worksheets("Variance Template").Copy after:=wb.Worksheets(wb.Worksheets.Count)
    
    Set newWs = wb.Worksheets(wb.Worksheets.Count) 'get the copy
    With newWs
        .Visible = True
        .Name = ws.Range("D6").Value
        .Tab.Color = RGB(146, 208, 80)
        .Range("F2").Value = ws.Range("D6").Value
        .Range("L2").Value = ws.Range("Q8").Value
        .Range("P2").Value = ws.Range("Q12").Value
        .Activate
    End With
    Application.ErrorCheckingOptions.BackgroundChecking = False

End Sub