Select range that are auto-filtered and have certain text value

[tmdgus]

Hi, I need some help with filtering and selecting specific cells with multiple criteria.
I have a table with a list of fruits and their expiry date.
I have tried using VBA to auto-filter the fruit data that are expiring in current month. (xlFilterThisMonth).
However, I only want to select data of apples that are expiring within a month, then copy and paste the selected data to sheet2.
Can anyone help me how to do so?

FruitList.xlsm
	A	B
1	Fruit	Expiry Date
2	Apple	13/9/2022
3	Banana	15/11/2022
4	Apple	19/10/2022
5	Citrus	28/10/2022
6	Banana	30/9/2022
7	Grape	1/10/2022
8	Apple	11/10/2022
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A2:A8	List	Apple,Banana,Citrus,Grape

 

[DanteAmor]

Try this:

Sub data_expires_this_month()
  With Sheets("Sheet1")
    .Range("A1").AutoFilter Field:=2, Criteria1:=xlFilterThisMonth, Operator:=xlFilterDynamic
    .AutoFilter.Range.Copy Sheets("Sheet2").Range("A1")
    .ShowAllData
  End With
End Sub

 

[tmdgus]

Hi Thank you for your help!
I tried with your code and it seems like the expiring data are copied and pasted well, but I want to copy and paste data only if the fruit is apple and it is expiring soon. Any suggestions on this?
Thank you!!

 

[kevin9999]

Sub Expiring_Apples()
    With Worksheets("Sheet1").Range("A1").CurrentRegion
        .AutoFilter 1, "Apple"
        .AutoFilter 2, 7, 11
        .Offset(1).Resize(.Rows.Count - 1).Copy Worksheets("Sheet2").Range("A1")
        .AutoFilter
    End With
End Sub

 

[tmdgus]

Sub Expiring_Apples()
    With Worksheets("Sheet1").Range("A1").CurrentRegion
        .AutoFilter 1, "Apple"
        .AutoFilter 2, 7, 11
        .Offset(1).Resize(.Rows.Count - 1).Copy Worksheets("Sheet2").Range("A1")
        .AutoFilter
    End With
End Sub

Thank you it worked !!

 

[kevin9999]

Thank you it worked !!

Good to hear

 

[DanteAmor]

...l, but I want to copy and paste data only if the fruit is apple ...

Try this:

Sub data_expires_this_month()
  With Sheets("Sheet1")
    .Range("A1").AutoFilter 1, "Apple"
    .Range("A1").AutoFilter field:=2, Criteria1:=xlFilterThisMonth, Operator:=xlFilterDynamic
    .AutoFilter.Range.Copy Sheets("Sheet2").Range("A1")
    .ShowAllData
  End With
End Sub