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


New Member
Apr 11, 2019
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


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


Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...