Autofilter Multiple Criteria, Copy and Paste Visible cells only excluding header
Results 1 to 2 of 2

Thread: Autofilter Multiple Criteria, Copy and Paste Visible cells only excluding header
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2013
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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-...pt-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!

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,321
    Post Thanks / Like
    Mentioned
    50 Post(s)
    Tagged
    14 Thread(s)

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

    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"
        DateToday = Date
        
        If Sheets(CopySheet).AutoFilterMode Then Sheets(CopySheet).AutoFilterMode = False
        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, Operator:=xlFilterValues, Criteria2:=Array(2, Format(DateToday, "mm/dd/yyyy"))
            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
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •