I received help from here a while back regarding macro code (PREVIOUS THREAD => macro if data in column a = set word to insert that column into sheet 2 or 3 based on word). I am having a problem now where if data is changed in the main spreadsheet the secondary spreadsheets are retaining that formula rather than clearing it from the previous secondary and putting it to a new secondary. Below is the code that is being used in the document.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Long
Dim rng As Range
Dim ws As Worksheet
If Selection.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then
On Error Resume Next
Set ws = Sheets(Range("A" & Target.Row).Value)
On Error GoTo 0
If ws Is Nothing Then
MsgBox "That team does not have a tab created"
Else
With ws
lRow = .Range("A" & Rows.Count).End(xlUp).Row + 1
.Cells(lRow, 1).Value = Range("A" & Target.Row).Value
For Each rng In .Range("B" & lRow & ":J" & lRow)
rng.Formula = "='CRH Projects'!" & Left(rng.Address(False, False), 1) & Target.Row
Next rng
End With
End If
End If
End Sub
Bookmarks