Autofilter Multiple Criteria, Copy and Paste Visible cells only excluding header

scapaflow

New Member
Joined
Feb 13, 2013
Messages
4
Requesting help with setting up a macro to autofilter multiple criteria, copying visible data only, and selecting and pasting sections of the data at a time into another worksheet in the file.


Here is a previous post that works for single criteria:


https://www.mrexcel.com/forum/excel...le-cells-after-auto-filter-except-header.html




I am using the code below and getting this error


"Run-time error '1004':
Application-defined or object-defined error"




Sub Filter()
Dim DateToday As Date
DateToday = Date$
Application.ScreenUpdating = True
Dim Lastcolumn As Long
Dim CopySheet As String
Dim PastSheet As String
CopySheet = "Absent_General"
PasteSheet = "Absent"
Lastcolumn = Sheets(CopySheet).Cells(1, Columns.Count).End(xlToLeft).Column
Lastrow = Sheets(PasteSheet).Cells(Rows.Count, "A").End(xlUp).Row + 1


With Sheets(CopySheet).Range(Cells(1, 1), Cells(Cells(Rows.Count, "A").End(xlUp).Row, Lastcolumn))
.AutoFilter Field:=13, Criteria1:="Absent"
.AutoFilter Field:=14, Criteria1:=DateToday
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).copy Sheets(PasteSheet).Cells(Lastrow, 1)
End With
Sheets(CopySheet).AutoFilterMode = False
Application.ScreenUpdating = True
End Sub


I am using criteria DateToday as a variable assigned with today's date. I need to search the data on the first worksheet named 'Absent_General' and paste it into the 'Absent' worksheet. In the original code, it only used 1 criteria for filtering, copying the sections, and pasting. I need to add 2 criteria with the code:


.AutoFilter Field:=13, Criteria1:="Absent"
.AutoFilter Field:=14, Criteria1:=DateToday


And its not working. Any help / advice would be greatly appreciated!
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Try the following:

(Do not put DateToday = Date$ only DateToday = Date)

Code:
Sub Filter()
    Dim DateToday As Date
    Dim Lastcolumn As Long, u as Long
    Dim CopySheet As String
    Dim PastSheet As String
    
    Application.ScreenUpdating = False
    
    CopySheet = "Absent_General"
    PasteSheet = "Absent"
[COLOR=#0000ff]    DateToday = Date[/COLOR]
    
[COLOR=#0000ff]    If Sheets(CopySheet).AutoFilterMode Then Sheets(CopySheet).AutoFilterMode = False[/COLOR]
    Lastcolumn = Sheets(CopySheet).Cells(1, Columns.Count).End(xlToLeft).Column
    Lastrow = Sheets(PasteSheet).Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    With Sheets(CopySheet).Range(Cells(1, 1), Cells(Cells(Rows.Count, "A").End(xlUp).Row, Lastcolumn))
        .AutoFilter Field:=13, Criteria1:="Absent"
[COLOR=#0000ff]        .AutoFilter Field:=14, Operator:=xlFilterValues, Criteria2:=Array(2, Format(DateToday, "mm/dd/yyyy"))[/COLOR]
        u = Range("N" & Rows.Count).End(xlUp).Row
        If u = 1 Then
            MsgBox "There is no data to copy"
        Else
            .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy Sheets(PasteSheet).Cells(Lastrow, 1)
            MsgBox "Copied data "
        End If
    End With
    If Sheets(CopySheet).AutoFilterMode Then Sheets(CopySheet).AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,165
Messages
5,594,622
Members
413,918
Latest member
Mikey_C

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top