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

 

[Needinghlp]

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

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.

I'll see what I can do.. would it make a difference if I'm running this code from sheet 1?

 

[vcoolio]

Hello Needinghlp,

It doesn't matter which sheet you run it from.

I've just tested both codes in a sample of how I'd guess your workbook is set out and both work exactly as they should. But of course I don't really know how it is set out which is why a sample from you will come in handy.

Cheerio,
vcoolio.

 

[Needinghlp]

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.

Hi vcoolio - just wanted to let you know the code is working perfectly. I looked at this again with fresh eyes this morning and ended up closing my workbook, reopening and coding it fresh again and it's working perfectly. Thanks so much for your help, I can't tell you how much I appreciate it.

 

[vcoolio]

You're welcome Needinghlp. Glad to have been able to assist and thanks for the feedback.
Nothing quite like a good night's sleep!

Cheerio,
vcoolio.