Change_Event macro dismiss table1 copy reinstate Table1
I get an error on this line where I try to reinstate Table1 to the Summary sheet
row 3 on down to last row of data.
..ListObjects.Add(xlSrcRange, Range("$A$3:$D$" & tRow), , xlYes).Name = _
"Table1"
This line is used in another non-event code and works there. It is inside a With Sheets("Summary") statement in that code also.
Thanks,
Howard
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Intersect(Target, Range("A:D")) Is Nothing Then Exit Sub
If LCase(ActiveSheet.Name) = "summary" Then GoTo done
If LCase(ActiveSheet.Name) = "begin blad" Then GoTo done
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Dim aRow As Long
Dim aRng As Range
Dim tRow As Long, i As Long
aRow = Target.Row
Set aRng = Range("A" & aRow).Resize(1, 4)
If Application.WorksheetFunction.CountA(aRng) = 4 Then
With Sheets("Summary")
For i = 1 To .ListObjects.Count
.ListObjects(i).Unlist
Next
aRng.Copy .Range("A" & Rows.Count).End(xlUp)(2)
'**** reinstate Table1 to Summary sheet *****
tRow = Cells(Rows.Count, "A").End(xlUp).Row
.ListObjects.Add(xlSrcRange, Range("$A$3:$D$" & tRow), , xlYes).Name = _
"Table1"
.ListObjects("Table1").TableStyle = "TableStyleLight2"
End With
End If
On Error GoTo aft_error
aft_error:
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
done:
End Sub
|