Helpmepls
04-25-2021, 03:54 AM
Hi everyone,
I have some code, below, and when it gets to the row in bold blue text (.Autofilter 19, ">"& pDt) I get a Run-time error '1004': AutoFilter method of Range class failed error. I wondered if anyone might happen to know why that's happening.
Some background on the code and what I'm do.. I have 2 sheets (Sheet1 & Sheet2). Sheet 2 has 2 columns (R & S) that contain a list of different dates. From sheet 1, I am running the code below to identify entries on sheet 2 that in column R are below 1/10/2021 and in column S have a date greater than 30/06/2021. On sheet2 in cell BD1 I have referenced the date 30/06/2021 and in BE1 I have referenced the date 1/10/2021
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("A2", Worksheets("Sheet2").Range("AN" & Worksheets("Sheet2").Rows.Count).End(xlup))
.AutoFilter 19, ">" & pDt
.AutoFilter 18, "<" & pDu
.columns("A:AN").Offset(1).Copy
Worksheets("Sheet1").Range("A" & Rows.Count).End(3) (2).PasteSpecial xlValues
.AutoFilter
End With
End Sub
I have some code, below, and when it gets to the row in bold blue text (.Autofilter 19, ">"& pDt) I get a Run-time error '1004': AutoFilter method of Range class failed error. I wondered if anyone might happen to know why that's happening.
Some background on the code and what I'm do.. I have 2 sheets (Sheet1 & Sheet2). Sheet 2 has 2 columns (R & S) that contain a list of different dates. From sheet 1, I am running the code below to identify entries on sheet 2 that in column R are below 1/10/2021 and in column S have a date greater than 30/06/2021. On sheet2 in cell BD1 I have referenced the date 30/06/2021 and in BE1 I have referenced the date 1/10/2021
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("A2", Worksheets("Sheet2").Range("AN" & Worksheets("Sheet2").Rows.Count).End(xlup))
.AutoFilter 19, ">" & pDt
.AutoFilter 18, "<" & pDu
.columns("A:AN").Offset(1).Copy
Worksheets("Sheet1").Range("A" & Rows.Count).End(3) (2).PasteSpecial xlValues
.AutoFilter
End With
End Sub