Need help with code below -
1.If any new name is added to pre existing data or new entry in column C (2.planning) to always add to end of list in col D and E Sheet (4. mobile)
2.If data deleted from sheet(2. planning) column C -must delete complete row in sheet (4. mobile)
see attached testfile :
Try adding new name from drop down list sheet 2.planning column C row 13 - New name selected should be end of list in 4. mobile (Currently it inserts row inbetween existing rows)
1.If any new name is added to pre existing data or new entry in column C (2.planning) to always add to end of list in col D and E Sheet (4. mobile)
2.If data deleted from sheet(2. planning) column C -must delete complete row in sheet (4. mobile)
see attached testfile :
Try adding new name from drop down list sheet 2.planning column C row 13 - New name selected should be end of list in 4. mobile (Currently it inserts row inbetween existing rows)
Code:
Private Sub Worksheet_Activate()
Dim r As Range, a, i As Long, e, x
Application.EnableEvents = False
With Sheets("2. Planning")
If .Range("c" & Rows.Count).End(xlUp).Row > 12 Then
a = .Range("c12", .Range("c" & Rows.Count).End(xlUp)).Resize(, 2).Value
End If
End With
If IsArray(a) Then
With CreateObject("Scripting.Dictionary")
.CompareMode = 1
For i = 2 To UBound(a, 1)
a(i, 1) = Application.Trim(a(i, 1))
If a(i, 1) <> "" Then
For Each e In Split(a(i, 1), ";")
x = Split(Application.Trim(e))
ReDim Preserve x(1)
.Item(Trim$(e)) = x
Next
End If
Next
a = Application.Index(.items, 0, 0): i = .Count
End With
End If
With [d18:e18]
.Resize(Rows.Count - .Row - 1).ClearContents
If IsArray(a) Then
.Resize(i).Value = a
End If
End With
Application.EnableEvents = True
End Sub