Applying autofilter then copying the sheets in a summary sheet from an excel workbook

[coder1234]

I am trying to apply autofilter and then copy the filtered results in one sheets using the below code .
But it is throwing an "Object Variable not set error "

Please help

Code


Sub CopyDataWithoutHeaders()
Dim ws As Worksheet, DestSh As Worksheet, Rng As Range
Set DestSh = ThisWorkbook.Sheets("Sheet16")


With Application
.ScreenUpdating = False
.EnableEvents = False
End With


For Each ws In ThisWorkbook.Sheets
If ws.Name <> "Format" And ws.Name <> "Lookups" And ws.Name <> DestSh.Name Then


'the below line will not select the complete range if a cell is empty in column 1
'it can be changed to the way you want.


Set Rng = ws.Range("A1", ws.Range("A1").End(xlDown).End(xlToRight))


With Rng 'will copy all the range except the header row
.AutoFilter Field:=22, Criteria1:="Ready to import", Operator:=xlAnd
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy
End With


'test if the first cell is empty before pasting
If DestSh.Range("A1") = "" Then
DestSh.Cells(Rows.Count, "A").End(xlUp).PasteSpecial xlPasteValues


Else: DestSh.Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
End If


'clean up each worksheet
ws.AutoFilterMode = False
Application.CutCopyMode = False
Next ws


With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End


/code