Autofiltering 2 colums based on date

[Needinghlp]

Hi experts. I have a worksheet (sheet2) that has a list of dates in columns R (start date) and columns S (end date).
Using autofilter, I am trying to identify which rows have dates that are below 1/10/2021 in column R AND column S has a date greater than 30/06/2021. On sheet 2 in cell BD1 I have the date 30/06/2021 and in BE1 I have the date 1/10/2021
I then want to copy the rows (column A to AN) that meet these criteria and paste them to another sheet (sheet1)
Below is my current code... it partially works but it is actually pasting rows with a start date after 1/10/2021 (all the end dates are after 30/06/2021)
The really annoying thing is I fixed this myself last week, but lost my work and now can't remember what I did to fix it... very grateful for any help.

Private Sub CommandButton5_Click()

Dim pDt As Long: pDt = Worksheets("sheet2").[BD1].Value
Dim pDu As Long: pDu = Worksheets("sheet2").[BE1].Value

With Worksheets("sheet2").Range("R3", Worksheets("sheet2").Range("R" & Worksheets("sheet2").Rows.Count).End(xlUp))
.AutoFilter 1, "<" & pDu
With Worksheets("sheet2").Range("S3", Worksheets("sheet2").Range("S" & Worksheets("sheet2").Rows.Count).End(xlUp))
.AutoFilter 1, ">" & pDt
.Offset(1, -18).Resize(, 40).Copy
Worksheets("sheet1").Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
.AutoFilter

End With

End with

End Sub

 

[maabadi]

Try this:

Private Sub CommandButton5_Click()
        With Sheet2.[A1].CurrentRegion
                .AutoFilter 6, "<" & [L1]
                .AutoFilter 7, ">" & [K1]
                .Offset(0).EntireRow.Copy
                Sheet1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
                .AutoFilter
        End With
End Sub

 

[vcoolio]

Hello Needinghlp,

Is row3 your headings row or is it row2?

Cheerio,
vcoolio.

 

[Needinghlp]

Hello Needinghlp,

Is row3 your headings row or is it row2?

Cheerio,
vcoolio.

Hi vcoolio - it's row 2.

 

[maabadi]

Sorry try this:

Private Sub CommandButton5_Click()
        With Sheet2.[A2].CurrentRegion
                .AutoFilter 18, "<" & Sheet1.[BE1]
                .AutoFilter 19, ">" & Sheet1.[BD1]
                .Offset(1).EntireRow.Copy
                Sheet1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
                .AutoFilter
        End With
End Sub

 

[vcoolio]

Thanks Needinghlp.

Here are a couple of options for you:-

Private Sub CommandButton5_Click()

        Dim pDt As Long: pDt = Sheet2.[BD1].Value
        Dim pDu As Long: pDu = Sheet2.[BE1].Value

Application.ScreenUpdating = False

        With Sheet2.Range("A2", Sheet2.Range("BE" & Sheet2.Rows.Count).End(xlUp)) '---->Assumes BE is your last column.
                .AutoFilter 19, ">" & pDt
                .AutoFilter 18, "<" & pDu
                .Columns("A:AN").Offset(1).Copy
                Sheet1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
                .AutoFilter
        End With

Application.ScreenUpdating = True

End Sub

OR

Private Sub CommandButton5_Click()

        Dim pDt As Long: pDt = Sheet2.[BD1].Value
        Dim pDu As Long: pDu = Sheet2.[BE1].Value

Application.ScreenUpdating = False

        With Sheet2.Range("A2", Sheet2.Range("BE" & Sheet2.Rows.Count).End(xlUp)) '---->Assumes BE is your last column.
                .AutoFilter 19, ">" & pDt
                .AutoFilter 18, "<" & pDu
                Sheet2.Range("A3", Sheet2.Range("AN" & Sheet2.Rows.Count).End(xlUp)).Copy
                Sheet1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
                .AutoFilter
        End With

Application.ScreenUpdating = True

End Sub

I've used the sheet codes (Sheet1 and Sheet2) in both the above codes.
As your headings are in row2, I've assumed that you have other text etc. in row1 which would make the CurrentRegion method error.

I hope that this helps.

Cheerio,
vcoolio.

 

[Needinghlp]

Thanks Needinghlp.

Here are a couple of options for you:-

Private Sub CommandButton5_Click()

        Dim pDt As Long: pDt = Sheet2.[BD1].Value
        Dim pDu As Long: pDu = Sheet2.[BE1].Value

Application.ScreenUpdating = False

        With Sheet2.Range("A2", Sheet2.Range("BE" & Sheet2.Rows.Count).End(xlUp)) '---->Assumes BE is your last column.
                .AutoFilter 19, ">" & pDt
                .AutoFilter 18, "<" & pDu
                .Columns("A:AN").Offset(1).Copy
                Sheet1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
                .AutoFilter
        End With

Application.ScreenUpdating = True

End Sub

OR

Private Sub CommandButton5_Click()

        Dim pDt As Long: pDt = Sheet2.[BD1].Value
        Dim pDu As Long: pDu = Sheet2.[BE1].Value

Application.ScreenUpdating = False

        With Sheet2.Range("A2", Sheet2.Range("BE" & Sheet2.Rows.Count).End(xlUp)) '---->Assumes BE is your last column.
                .AutoFilter 19, ">" & pDt
                .AutoFilter 18, "<" & pDu
                Sheet2.Range("A3", Sheet2.Range("AN" & Sheet2.Rows.Count).End(xlUp)).Copy
                Sheet1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
                .AutoFilter
        End With

Application.ScreenUpdating = True

End Sub

I've used the sheet codes (Sheet1 and Sheet2) in both the above codes.
As your headings are in row2, I've assumed that you have other text etc. in row1 which would make the CurrentRegion method error.

I hope that this helps.

Cheerio,
vcoolio.

Thanks vcoolio.. do you know why I would get a Run-time error '1004': AutoFilter method of Range class failed on the .AutoFilter 19, ">" & pDt row?

 

[maabadi]

1. Are you Test My Code at Post #5?
2. Are you have Data at Cell BD1 & BE1?
3. Are you Change Range to Your Range? I think you should Change This Line :

With Sheet2.Range("A2", Sheet2.Range("BE" & Sheet2.Rows.Count).End(xlUp))

to This:

With Sheet2.Range("A2", Sheet2.Range("AN" & Sheet2.Rows.Count).End(xlUp))

 

[vcoolio]

Hello Needinghlp,

I'm not really sure without seeing your workbook. Could you please upload a sample to a file sharing site such as Drop Box or WeTransfer with desensitised data then post the link to your file back here. Make sure that the sample is an exact replica of your actual workbook.

Cheerio,
vcoolio.

 

[Needinghlp]

Sorry try this:

Private Sub CommandButton5_Click()
        With Sheet2.[A2].CurrentRegion
                .AutoFilter 18, "<" & Sheet1.[BE1]
                .AutoFilter 19, ">" & Sheet1.[BD1]
                .Offset(1).EntireRow.Copy
                Sheet1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
                .AutoFilter
        End With
End Sub

Thanks maabadi.. I get a Run-time error '1004': AutoFilter method of Range class failed on the line .AutoFilter 18, "<" & Sheet1.[BE1]
I did change Sheet1.[BE1] to Sheet2.[BE1] because it's sheet 2 BE1 where the date reference is.