Quote:
|
The error shows in your code on the highlighted line .ListObjects(destTable).HeaderRowRange.Select
|
Opps... eliminate the error by adding the Else Exit Sub bits here
Code:
If UCase(Target.Value) = "NO" Then
destTable = "_" & Replace(Sh.Name, " ", "_")
ray = Split(Cells(Target.Row, "B").Value & "|" & Cells(Target.Row, "D").Value & "|" & Cells(Target.Row, "J").Value, "|")
Else
Exit Sub
End If
Quote:
|
Not sure how to set that code up to remove the copied data when "YES" or blank cell selected.
|
The code for removal of the data from table will need to be in the above Else ahead of Exit Sub.
What is there to uniquely, positively, absolutely and unmistakenly identify the line to be removed?
(A record ID number would be a great thing to have)
Quote:
|
Why wont that pick up the copied data?
|
It was being copied as text.
The revised code
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'limit to sheets whose name starts with 5 digits
If Not IsNumeric(Left(Sh.Name, 5)) Then Exit Sub
'limit to single cell in column K
If Target.Count > 1 Or Target.Column <> 11 Then Exit Sub
Dim ray As Variant
Dim oLo As ListObject
Dim destTable As String
Dim HderRow As Long
Dim writeRow As Long
Dim i As Integer
If UCase(Target.Value) = "NO" Then
destTable = "_" & Replace(Sh.Name, " ", "_")
ray = Split(Cells(Target.Row, "B").Value & "|" & Cells(Target.Row, "D").Value & "|" & Cells(Target.Row, "J").Value, "|")
Else
'removal would be done here
Exit Sub
End If
Application.ScreenUpdating = False
With Sheets("MONTHLY GOE RECONCILIATION")
.Select
.ListObjects(destTable).HeaderRowRange.Select
HderRow = Selection.Row
For i = 1 To 99
If IsEmpty(.Cells(HderRow + i, 5)) Then Exit For
Next i
writeRow = HderRow + i
Application.EnableEvents = False
.Cells(writeRow, 2) = ray(0)
.Cells(writeRow, 3) = ray(1)
.Cells(writeRow, 4) = ray(2) * 1
Application.EnableEvents = True
End With
Sh.Select
Application.ScreenUpdating = True
End Sub
Hope that rectifies some of the issues.