Announcement

Collapse
No announcement yet.

Autofilter.range.copy: Object variable or With block variable not set

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Autofilter.range.copy: Object variable or With block variable not set



    I am running the following code that copies filtered data in a sheet and pastes it onto another sheet.

    Sub ApplyFilter()

    Sheets("DataFilter").Activate
    Sheets("DataFilter").Range("A3:AZZ4000").Clear
    Sheets("Data").Activate
    ActiveSheet.AutoFilter.Range.Copy
    Sheets("DataFilter").Activate
    Range("A3").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

    End Sub

    Sometimes it works, and sometimes it shows "Run-time error '91': Object variable or With block variable not set". The debug points to the autofilter.range.copy line. Is there something I am missing?

    Thanks in advance!

    Matt



  • #2
    Try this

    Sub ApplyFilter()

    Sheets("DataFilter").Cells.Delete shift:=xlUp

    With Sheets("Data")
    .AutoFilterMode = False
    With .Range("Table1")
    .AutoFilter
    .SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("DataFilter").Range("A3")
    End With
    End With

    Application.CutCopyMode = False

    End Sub

    Comment


    • #3


      Hi, thanks so much for your response! Much appreciated.

      However, that code removes the autofilter in Sheets("Data") and copies all rows in the table, including those hidden by the autofilter. After I run the code, I see it's copied all rows, I go back to "Data" sheet and the filter is removed, all rows showing.

      Is there a way of using the above code that preserves the filter selections?

      Thanks
      Matt

      Comment

      Working...
      X