This is worksheet event code. This is updating data for 'Seating by Alpha' sheet. When you add data in 'Seats' sheet it will up date in Seating by Alpha sheet and also sorts on column A.
When Data is deleted that particular data is removed in Seating by Alpha sheet.
Try this and come back. If it is Ok we will go to next sheet.
Note the changes in Seating by Alpha sheet.
For column C no data in Seats Sheet.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target <> "" Then
If WorksheetFunction.IsText(Target.Value) And IsNumeric(Target.Offset(-1, 0).Value) Then
Dim R(1 To 1, 1 To 6)
Dim M, Lr&
On Error Resume Next
M = Split(Target, " ")
R(1, 1) = M(UBound(M))
R(1, 2) = M(0)
If UBound(M) = 2 Then R(1, 2) = R(1, 2) & " " & M(1)
R(1, 4) = Target.Offset(-1, 0).End(xlToLeft)
R(1, 4) = Target.Offset(-1, 0).End(xlToRight)
R(1, 5) = Target.Offset(-1, 0)
If Target.Column < Range("J1").Column Then
R(1, 6) = "Left"
ElseIf Target.Column < Range("AA1").Column Then
R(1, 6) = "Centre"
Else
R(1, 6) = "Right"
End If
With Sheets("Seating by Alpha")
Lr = .Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
.Range("A" & Lr & ":F" & Lr) = R
.Sort.SortFields.Clear
.Range("A1:F" & Lr).Sort Key1:=.Range("A1"), Header:=xlYes, Order1:=xlAscending
End With
End If
Else
Dim Ro$, St$, Sec$, DelRo&
Ro = Target.Offset(-1, 0).End(xlToLeft)
Ro = Target.Offset(-1, 0).End(xlToRight)
St = Target.Offset(-1, 0)
If Target.Column < Range("J1").Column Then
Sec = "Left"
ElseIf Target.Column < Range("AA1").Column Then
Sec = "Centre"
Else
Sec = "Right"
End If
With Sheets("Seating by Alpha")
DelRo = Evaluate("Match(""" & Ro & St & Sec & """,'" & .Name & "'!D:D&'" & .Name & "'!E:E&'" & .Name & "'!F:F" & ", 0)")
.Range("A" & DelRo & ":F" & DelRo).Delete (xlUp)
End With
End If
Application.EnableEvents = True
End Sub
Bookmarks