Please Help - Criteria wording - Three (3) date ranges in for advance filter.

joach

New Member
Joined
Jul 15, 2022
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Hi,

I'm wanting to use advance filters to filter my data set.

I am wanting to follow up with the customer 8 months, 20 months and 56 months after the purchase date, but if the date is equal to or more than 12, 24 and 60 months from the purchase date I don't want to see the data in the new sheet.

In written terms I want to see the following data however I don't know how to type it in an Excel format / formulas.

follow up
1 year if todays date is between 8 months and 12 months from the purchase date
2 yearif todays date is between 20 months and 24 months from the purchase date
5 yearif todays date is between 56 months and 60 months from the purchase date

Could you please help me with the formatting / formulas for the above criteria.

In addition to this once the filtered data is on the new sheet 'follow up' I want to use conditional formatting with a colour gradient to show the following - green being the lower number (i.e 8 months), yellow being the mid point (i.e 10 months) and red being the deadline (i.e 12months).

If you could please help with the above that would be amazing.

If you need anything else please let me know.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
6,087
Office Version
  1. 365
Platform
  1. Windows
I am not clear on whether you are looking for a macro or not, so lets start with the Advanced Filter or possibly autofilter part.

The 2 columns in yellow are helper columns in case you just wanted to use Autofilter. You could reduce it down to a single helper column, the formula will just more complex.
K2 as a single column helper column becomes
Excel Formula:
=IF(AND(OR(INT(DATEDIF(F2,$N$4,"m")/12)=0,INT(DATEDIF(F2,$N$4,"m")/12)=1,INT(DATEDIF(F2,$N$4,"m")/12)=4),MOD(DATEDIF(F2,$N$4,"m"),12)>=8),"Yes","No")

The Advanced Filter cells (x2) are the criteria cells for use in an advanced filter, if you want to go that way.

I am using N4 instead of putting today directly into a formula, I just find that to be a more flexible option.

Ideally you should post some sample data in the XL2BB format. It makes it easier for people to help you and you are more likely to get a response.
It also means you need to do less reconfiguring at your end of any solution presented to you.

20220720 VBA Follow up Purchase Date Anniv joach.xlsm
ABCDEFGHIJKLMN
1Col1Col2Col3Col4Col5Purchase DateCol7Col8Col9MthsFollow UpAdvanced Filter Criteria
27/05/201586NoFALSE
37/06/201585No
47/07/201584NoSet today as20/07/2022
57/08/201583No
67/09/201582No
77/10/201581No
87/11/201580No
97/12/201579No
107/01/201678No
117/02/201677No
127/03/201676No
137/04/201675No
147/05/201674No
157/06/201673No
167/07/201672No
177/08/201671No
187/09/201670No
197/10/201669No
207/11/201668No
217/12/201667No
227/01/201766No
237/02/201765No
247/03/201764No
257/04/201763No
267/05/201762No
277/06/201761No
287/07/201760No
297/08/201759Yes
307/09/201758Yes
317/10/201757Yes
327/11/201756Yes
337/12/201755No
347/01/201854No
Sheet1
Cell Formulas
RangeFormula
J2:J34J2=DATEDIF(F2,$N$4,"m")
K2:K34K2=IF(AND(OR(INT(J2/12)=0,INT(J2/12)=1,INT(J2/12)=4),MOD(J2,12)>=8),"Yes","No")
M2M2=IF(AND(OR(INT(DATEDIF($F2,$N$4,"m")/12)=0,INT(DATEDIF($F2,$N$4,"m")/12)=1,INT(DATEDIF($F2,$N$4,"m")/12)=4),MOD(DATEDIF($F2,$N$4,"m"),12)>=8),TRUE,FALSE)
 
Last edited:

joach

New Member
Joined
Jul 15, 2022
Messages
17
Office Version
  1. 365
Platform
  1. Windows
I am not clear on whether you are looking for a macro or not, so lets start with the Advanced Filter or possibly autofilter part.

The 2 columns in yellow are helper columns in case you just wanted to use Autofilter. You could reduce it down to a single helper column, the formula will just more complex.
K2 as a single column helper column becomes
Excel Formula:
=IF(AND(OR(INT(DATEDIF(F2,$N$4,"m")/12)=0,INT(DATEDIF(F2,$N$4,"m")/12)=1,INT(DATEDIF(F2,$N$4,"m")/12)=4),MOD(DATEDIF(F2,$N$4,"m"),12)>=8),"Yes","No")

The Advanced Filter cells (x2) are the criteria cells for use in an advanced filter, if you want to go that way.

I am using N4 instead of putting today directly into a formula, I just find that to be a more flexible option.

Ideally you should post some sample data in the XL2BB format. It makes it easier for people to help you and you are more likely to get a response.
It also means you need to do less reconfiguring at your end of any solution presented to you.

20220720 VBA Follow up Purchase Date Anniv joach.xlsm
ABCDEFGHIJKLMN
1Col1Col2Col3Col4Col5Purchase DateCol7Col8Col9MthsFollow UpAdvanced Filter Criteria
27/05/201586NoFALSE
37/06/201585No
47/07/201584NoSet today as20/07/2022
57/08/201583No
67/09/201582No
77/10/201581No
87/11/201580No
97/12/201579No
107/01/201678No
117/02/201677No
127/03/201676No
137/04/201675No
147/05/201674No
157/06/201673No
167/07/201672No
177/08/201671No
187/09/201670No
197/10/201669No
207/11/201668No
217/12/201667No
227/01/201766No
237/02/201765No
247/03/201764No
257/04/201763No
267/05/201762No
277/06/201761No
287/07/201760No
297/08/201759Yes
307/09/201758Yes
317/10/201757Yes
327/11/201756Yes
337/12/201755No
347/01/201854No
Sheet1
Cell Formulas
RangeFormula
J2:J34J2=DATEDIF(F2,$N$4,"m")
K2:K34K2=IF(AND(OR(INT(J2/12)=0,INT(J2/12)=1,INT(J2/12)=4),MOD(J2,12)>=8),"Yes","No")
M2M2=IF(AND(OR(INT(DATEDIF($F2,$N$4,"m")/12)=0,INT(DATEDIF($F2,$N$4,"m")/12)=1,INT(DATEDIF($F2,$N$4,"m")/12)=4),MOD(DATEDIF($F2,$N$4,"m"),12)>=8),TRUE,FALSE)

Hi Alex,

Thank you for the reply.

Me being a novice most of the stuff that you said went straight over my head.

As a result I have provided the following sample set.

All Suburbs (version 2).xlsb.xlsm
ABCDEFGHIJK
1Order NumberAddressStreetSuburbDescriptionEstimated costLodged dateCustomer nameCSOpurchase typePurchase date
21AAXXXXXX$0.00xx/xx/xxXXXXYYYYYCash21/04/2021
32BBXXXXXX$100.00xx/xx/xxXXXXYYYYYpaypal10/09/2019
43CCXXXXXX$200.00xx/xx/xxXXXXYYYYYCredit Card14/10/2019
54AAXXXXXX$300.00xx/xx/xxXXXXYYYYYdebit card23/10/2018
65BBXXXXXX$400.00xx/xx/xxXXXXYYYYYCash26/03/2019
76CCXXXXXX$500.00xx/xx/xxXXXXYYYYYpaypal26/03/2019
87AAXXXXXX$600.00xx/xx/xxXXXXYYYYYCredit Card2/08/2018
98BBXXXXXX$700.00xx/xx/xxXXXXYYYYYdebit card22/08/2019
109CCXXXXXX$800.00xx/xx/xxXXXXYYYYYdebit card20/09/2017
1110AAXXXXXX$900.00xx/xx/xxXXXXYYYYYpaypal7/05/2019
1211BBXXXXXX$1,000.00xx/xx/xxXXXXYYYYYCredit Card7/05/2019
1312CCXXXXXX$1,100.00xx/xx/xxXXXXYYYYYdebit card8/05/2018
1413AAXXXXXX$1,200.00xx/xx/xxXXXXYYYYYCash8/05/2018
1514BBXXXXXX$1,300.00xx/xx/xxXXXXYYYYYpaypal30/06/2020
1615CCXXXXXX$1,400.00xx/xx/xxXXXXYYYYYCredit Card9/03/2018
1716AAXXXXXX$1,500.00xx/xx/xxXXXXYYYYYdebit card2/04/2019
Sheet7
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K2:K1794Expression=" =EDATE(today(),-5)"textNO
A2:K9315Cell Valuecontains "2.00"textNO
A2:K9315Cell Valuecontains "12.00"textNO
A2:K9315Cell Valuecontains "trial period"textNO
A2:K9315Cell Valuecontains "0pm"textNO
A2:K9315Cell Valuecontains "0am"textNO
A2:K9315Cell Valuecontains "midnight"textNO
A2:K9315Cell Valuecontains "0 am"textNO
A2:K9315Cell Valuecontains "0 pm"textNO
A2:K9315Cell Valuecontains "operation"textNO
A2:K9315Cell Valuecontains "small bar"textNO
A2:K9315Cell Valuecontains "food"textNO
A2:K9315Cell Valuecontains "hour"textNO


I would also like it if i could have a second filter that does not include cash payments for a secondary sheet

cheers

Joach[/RANGE]
 
Last edited:

joach

New Member
Joined
Jul 15, 2022
Messages
17
Office Version
  1. 365
Platform
  1. Windows
I am not clear on whether you are looking for a macro or not, so lets start with the Advanced Filter or possibly autofilter part.

The 2 columns in yellow are helper columns in case you just wanted to use Autofilter. You could reduce it down to a single helper column, the formula will just more complex.
K2 as a single column helper column becomes
Excel Formula:
=IF(AND(OR(INT(DATEDIF(F2,$N$4,"m")/12)=0,INT(DATEDIF(F2,$N$4,"m")/12)=1,INT(DATEDIF(F2,$N$4,"m")/12)=4),MOD(DATEDIF(F2,$N$4,"m"),12)>=8),"Yes","No")

The Advanced Filter cells (x2) are the criteria cells for use in an advanced filter, if you want to go that way.

I am using N4 instead of putting today directly into a formula, I just find that to be a more flexible option.

Ideally you should post some sample data in the XL2BB format. It makes it easier for people to help you and you are more likely to get a response.
It also means you need to do less reconfiguring at your end of any solution presented to you.

20220720 VBA Follow up Purchase Date Anniv joach.xlsm
ABCDEFGHIJKLMN
1Col1Col2Col3Col4Col5Purchase DateCol7Col8Col9MthsFollow UpAdvanced Filter Criteria
27/05/201586NoFALSE
37/06/201585No
47/07/201584NoSet today as20/07/2022
57/08/201583No
67/09/201582No
77/10/201581No
87/11/201580No
97/12/201579No
107/01/201678No
117/02/201677No
127/03/201676No
137/04/201675No
147/05/201674No
157/06/201673No
167/07/201672No
177/08/201671No
187/09/201670No
197/10/201669No
207/11/201668No
217/12/201667No
227/01/201766No
237/02/201765No
247/03/201764No
257/04/201763No
267/05/201762No
277/06/201761No
287/07/201760No
297/08/201759Yes
307/09/201758Yes
317/10/201757Yes
327/11/201756Yes
337/12/201755No
347/01/201854No
Sheet1
Cell Formulas
RangeFormula
J2:J34J2=DATEDIF(F2,$N$4,"m")
K2:K34K2=IF(AND(OR(INT(J2/12)=0,INT(J2/12)=1,INT(J2/12)=4),MOD(J2,12)>=8),"Yes","No")
M2M2=IF(AND(OR(INT(DATEDIF($F2,$N$4,"m")/12)=0,INT(DATEDIF($F2,$N$4,"m")/12)=1,INT(DATEDIF($F2,$N$4,"m")/12)=4),MOD(DATEDIF($F2,$N$4,"m"),12)>=8),TRUE,FALSE)

Hi Alex,

Thank you for the reply.

Me being a novice most of the stuff that you said went straight over my head.

As a result I have provided the following sample set.

All Suburbs (version 2).xlsb.xlsm
ABCDEFGHIJK
1Order NumberAddressStreetSuburbDescriptionEstimated costLodged dateCustomer nameCSOpurchase typePurchase date
21AAXXXXXX$0.00xx/xx/xxXXXXYYYYYCash21/04/2021
32BBXXXXXX$100.00xx/xx/xxXXXXYYYYYpaypal10/09/2019
43CCXXXXXX$200.00xx/xx/xxXXXXYYYYYCredit Card14/10/2019
54AAXXXXXX$300.00xx/xx/xxXXXXYYYYYdebit card23/10/2018
65BBXXXXXX$400.00xx/xx/xxXXXXYYYYYCash26/03/2019
76CCXXXXXX$500.00xx/xx/xxXXXXYYYYYpaypal26/03/2019
87AAXXXXXX$600.00xx/xx/xxXXXXYYYYYCredit Card2/08/2018
98BBXXXXXX$700.00xx/xx/xxXXXXYYYYYdebit card22/08/2019
109CCXXXXXX$800.00xx/xx/xxXXXXYYYYYdebit card20/09/2017
1110AAXXXXXX$900.00xx/xx/xxXXXXYYYYYpaypal7/05/2019
1211BBXXXXXX$1,000.00xx/xx/xxXXXXYYYYYCredit Card7/05/2019
1312CCXXXXXX$1,100.00xx/xx/xxXXXXYYYYYdebit card8/05/2018
1413AAXXXXXX$1,200.00xx/xx/xxXXXXYYYYYCash8/05/2018
1514BBXXXXXX$1,300.00xx/xx/xxXXXXYYYYYpaypal30/06/2020
1615CCXXXXXX$1,400.00xx/xx/xxXXXXYYYYYCredit Card9/03/2018
1716AAXXXXXX$1,500.00xx/xx/xxXXXXYYYYYdebit card2/04/2019
Sheet7
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K2:K1794Expression=" =EDATE(today(),-5)"textNO
A2:K9315Cell Valuecontains "2.00"textNO
A2:K9315Cell Valuecontains "12.00"textNO
A2:K9315Cell Valuecontains "trial period"textNO
A2:K9315Cell Valuecontains "0pm"textNO
A2:K9315Cell Valuecontains "0am"textNO
A2:K9315Cell Valuecontains "midnight"textNO
A2:K9315Cell Valuecontains "0 am"textNO
A2:K9315Cell Valuecontains "0 pm"textNO
A2:K9315Cell Valuecontains "operation"textNO
A2:K9315Cell Valuecontains "small bar"textNO
A2:K9315Cell Valuecontains "food"textNO
A2:K9315Cell Valuecontains "hour"textNO


I would also like it if i could have a second filter that does not include cash payments for a secondary sheet

cheers

Joach[/RANGE]
Hi Alex,

I don't no where the conditional formatting in my last message came from.
 

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
6,087
Office Version
  1. 365
Platform
  1. Windows
Although your initial post refers to Advance Filter and using formulas and formatting, I am assuming you want / need a macro to:
• Filter the data once using your criteria and outputting it to a new sheet
• Filter the above with an additional criteria of <> Cash and outputting it to a different new sheet.

What is the sheet name of the original data ?
What names do you want to use for the 2 new sheets and do the sheets already exist in the workbook ?
Can I assume the data in all 3 sheets will start at A1 ?

Alternatively your other thread says you are using Power Query, is that a better way to go for what you want ?
It also refers to using Excel Tables. Is the table you are using here and Actual Excel Table ? We would need to know the Table Name.

Also in the other thread I think you used Advanced Filter for the first time. Did you just want the formula to use on your sample data so you can manually apply the advanced filter ?
I am concerned that you are getting bits of a solution that then won't integrate to do you what you need.
 
Last edited:

joach

New Member
Joined
Jul 15, 2022
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Although your initial post refers to Advance Filter and using formulas and formatting, I am assuming you want / need a macro to:
• Filter the data once using your criteria and outputting it to a new sheet
• Filter the above with an additional criteria of <> Cash and outputting it to a different new sheet.

What is the sheet name of the original data ?
What names do you want to use for the 2 new sheets and do the sheets already exist in the workbook ?
Can I assume the data in all 3 sheets will start at A1 ?

Alternatively your other thread says you are using Power Query, is that a better way to go for what you want ?
It also refers to using Excel Tables. Is the table you are using here and Actual Excel Table ? We would need to know the Table Name.

Also in the other thread I think you used Advanced Filter for the first time. Did you just want the formula to use on your sample data so you can manually apply the advanced filter ?
I am concerned that you are getting bits of a solution that then won't integrate to do you what you need.
Hi Alex,

Thank you for the reply on here and my other thread.

As you read on my other thread I used power query to compile a number of sheets (34) to make a master sheet with all of the data point. From there I used advance filters to narrow down by searches to allow me to identify if a product contains the word 'beanie' etc.

Now that I have a sheet containing all of the entries that contain the terms 'beanie' I want to be able to create a new sheet that further refines the data to allow me to follow up with customers between certain dates to see how they have enjoyed the product.

In relation to your response under this thread I have tried my best to answer all of your questions:

What is the sheet name of the original data ? The sheet name of the sheet that contains the term 'beanie' is named 'Filtered Orders' whilst the master sheet is called 'All Products'. Both these Sheets are tables and are named Filtered_Orders and All_Products respectively.

What names do you want to use for the 2 new sheets and do the sheets already exist in the workbook ? I have created the sheet but they are currently blank they are called 'Follow Up 12 21 57 Months' and 'Follow Up 12 21 57 Months (No Cash)' - I will likely convert the sheets into tables too.

Can I assume the data in all 3 sheets will start at A1 ? Correct, the sheet above was taken from 'Filtered Orders' however as you can tell I have redacted most of the information.

Is Power Query a better way to go for what you want ? I'm honestly not sure.

Is the table you are using here and Actual Excel Table ? what is the Table Name? The table above is taken from my working table name is 'Filtered_Orders'

Did you just want the formula to use on your sample data so you can manually apply the advanced filter? I don't want to have to manually apply filters unless it is adding other criteria (i may be completely misunderstanding the question). I thought that the only way to get the refined data that I was wanting was by using advance filters, like I said I'm not super proficient with Excel at the moment.

Let me know if you need anything else.

thanks for the help thus far.

Joach
 

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
6,087
Office Version
  1. 365
Platform
  1. Windows
Give the code below a try. You will need to put it into a standard / regular VBA module.

I have created the sheet but they are currently blank they are called 'Follow Up 12 21 57 Months' and 'Follow Up 12 21 57 Months (No Cash)'
The (No Cash) sheet name can't exist it is too long.
Create a sheet called "Follow Up 12 21 57 (No Cash)" that is what I have used in this code.

If you don't know what to do, you might want to read this first:
VBA Code

VBA Code:
Sub CreateFollowUpSheets()

    ' Create 2 new sheets that meet follow up criteria
    ' Primary criteria Purchase date is within 4 months before the 1st, 2nd and 5th anniversary
  
    Dim shtSrc As Worksheet, shtFollowUp As Worksheet, shtFollowNoCsh As Worksheet
    Dim tblSrc As ListObject
    Dim rngSrc As Range, rngDest As Range, rngCrit As Range
    Dim srcRowFirst As Long, srcRowLast As Long, srcColLast As Long
    Dim strFormula As String

    Application.ScreenUpdating = False
     
    srcRowFirst = 1
  
    Set shtSrc = Worksheets("Filtered Orders")
    Set shtFollowUp = Worksheets("Follow Up 12 21 57 Months")
    Set shtFollowNoCsh = Worksheets("Follow Up 12 21 57 (No Cash)")
  
    Set tblSrc = Range("Filtered_Orders").ListObject

    With shtSrc
        srcColLast = tblSrc.Range.Columns(1).Column + tblSrc.Range.Columns.Count - 1
        Set rngSrc = tblSrc.Range
    End With
  
    strFormula = "=IF(OR("
    strFormula = strFormula _
                    & "AND($K2<=TODAY()," _
                    & "$K2>DATE(YEAR(TODAY())," & "MONTH(TODAY())-4,DAY(TODAY()))),"
    strFormula = strFormula _
                    & "AND($K2<=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))," _
                    & "$K2>DATE(YEAR(TODAY())-1,MONTH(TODAY())-4,DAY(TODAY()))),"
    strFormula = strFormula _
                    & "AND($K2<=DATE(YEAR(TODAY())-5,MONTH(TODAY()),DAY(TODAY()))," _
                    & "$K2>DATE(YEAR(TODAY())-5,MONTH(TODAY())-4,DAY(TODAY())))),TRUE,FALSE)"
  
    '--------------------------------------------------------
    ' Follow up sheet
    '--------------------------------------------------------
    With shtFollowUp
        .Cells.Clear
        Set rngDest = .Range("A1")
    End With
  
    With shtSrc
        ' Temporary range for criteria
        Set rngCrit = .Cells(srcRowFirst, srcColLast + 2).Resize(2, 1)
    End With
  
    ' Set Criteria
    rngCrit.Cells(1, 1).Value = "Date Criteria"
    rngCrit.Cells(2, 1).Value = strFormula

    ' Apply Filter and Copy to Destination
    rngSrc.AdvancedFilter xlFilterCopy, rngCrit, rngDest
    rngCrit.ClearContents
  
    '--------------------------------------------------------
    ' Follow up Excluding Cash Sheet
    '--------------------------------------------------------
    With shtFollowNoCsh
        .Cells.Clear
        Set rngDest = .Range("A1")
    End With
  
    With shtSrc
        ' Temporary range for criteria
        Set rngCrit = .Cells(srcRowFirst, srcColLast + 2).Resize(2, 2)
    End With
  
    ' Set Criteria
    rngCrit.Cells(1, 1).Value = "Date Criteria"
    rngCrit.Cells(2, 1).Value = strFormula
    rngCrit.Cells(1, 2).Value = "purchase type"
    rngCrit.Cells(2, 2).Value = "<>Cash"

    ' Apply Filter and Copy to Destination
    rngSrc.AdvancedFilter xlFilterCopy, rngCrit, rngDest
    rngCrit.ClearContents
    Set rngDest = rngDest.CurrentRegion
    rngDest.Columns.AutoFit

   Application.ScreenUpdating = True
End Sub
 
Last edited:

joach

New Member
Joined
Jul 15, 2022
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Give the code below a try. You will need to put it into a standard / regular VBA module.


The (No Cash) sheet name can't exist it is too long.
Create a sheet called "Follow Up 12 21 57 (No Cash)" that is what I have used in this code.

If you don't know what to do, you might want to read this first:
VBA Code

VBA Code:
Sub CreateFollowUpSheets()

    ' Create 2 new sheets that meet follow up criteria
    ' Primary criteria Purchase date is within 4 months before the 1st, 2nd and 5th anniversary
 
    Dim shtSrc As Worksheet, shtFollowUp As Worksheet, shtFollowNoCsh As Worksheet
    Dim tblSrc As ListObject
    Dim rngSrc As Range, rngDest As Range, rngCrit As Range
    Dim srcRowFirst As Long, srcRowLast As Long, srcColLast As Long
    Dim strFormula As String

    Application.ScreenUpdating = False
    
    srcRowFirst = 1
 
    Set shtSrc = Worksheets("Filtered Orders")
    Set shtFollowUp = Worksheets("Follow Up 12 21 57 Months")
    Set shtFollowNoCsh = Worksheets("Follow Up 12 21 57 (No Cash)")
 
    Set tblSrc = Range("Filtered_Orders").ListObject

    With shtSrc
        srcColLast = tblSrc.Range.Columns(1).Column + tblSrc.Range.Columns.Count - 1
        Set rngSrc = tblSrc.Range
    End With
 
    strFormula = "=IF(OR("
    strFormula = strFormula _
                    & "AND($K2<=TODAY()," _
                    & "$K2>DATE(YEAR(TODAY())," & "MONTH(TODAY())-4,DAY(TODAY()))),"
    strFormula = strFormula _
                    & "AND($K2<=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))," _
                    & "$K2>DATE(YEAR(TODAY())-1,MONTH(TODAY())-4,DAY(TODAY()))),"
    strFormula = strFormula _
                    & "AND($K2<=DATE(YEAR(TODAY())-5,MONTH(TODAY()),DAY(TODAY()))," _
                    & "$K2>DATE(YEAR(TODAY())-5,MONTH(TODAY())-4,DAY(TODAY())))),TRUE,FALSE)"
 
    '--------------------------------------------------------
    ' Follow up sheet
    '--------------------------------------------------------
    With shtFollowUp
        .Cells.Clear
        Set rngDest = .Range("A1")
    End With
 
    With shtSrc
        ' Temporary range for criteria
        Set rngCrit = .Cells(srcRowFirst, srcColLast + 2).Resize(2, 1)
    End With
 
    ' Set Criteria
    rngCrit.Cells(1, 1).Value = "Date Criteria"
    rngCrit.Cells(2, 1).Value = strFormula

    ' Apply Filter and Copy to Destination
    rngSrc.AdvancedFilter xlFilterCopy, rngCrit, rngDest
    rngCrit.ClearContents
 
    '--------------------------------------------------------
    ' Follow up Excluding Cash Sheet
    '--------------------------------------------------------
    With shtFollowNoCsh
        .Cells.Clear
        Set rngDest = .Range("A1")
    End With
 
    With shtSrc
        ' Temporary range for criteria
        Set rngCrit = .Cells(srcRowFirst, srcColLast + 2).Resize(2, 2)
    End With
 
    ' Set Criteria
    rngCrit.Cells(1, 1).Value = "Date Criteria"
    rngCrit.Cells(2, 1).Value = strFormula
    rngCrit.Cells(1, 2).Value = "purchase type"
    rngCrit.Cells(2, 2).Value = "<>Cash"

    ' Apply Filter and Copy to Destination
    rngSrc.AdvancedFilter xlFilterCopy, rngCrit, rngDest
    rngCrit.ClearContents
    Set rngDest = rngDest.CurrentRegion
    rngDest.Columns.AutoFit

   Application.ScreenUpdating = True
End Sub
Hey Alex,

Thank you so much for doing this for me.

I'm getting a Run-time error '91' at the following part - .Cells.Clear.
1658622859058.png



1658623487294.png


I note that i did changed the of the sheet name though out the code to 'shtActiveSalesCashless' to reflect the update name I have given the sheet. would that have made the error occur?

cheers,

Joach
 

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
6,087
Office Version
  1. 365
Platform
  1. Windows
If you don't have something that looks like these 3 lines in your code then it would cause your error,

VBA Code:
    Dim shtActiveSalesCashless As Worksheet                                 ' Declare the variable and the variable type
        
    Set shtActiveSalesCashless = Worksheets("PutInTheCorrectSheetName")     ' Point the Varable at the sheet
    
    With shtActiveSalesCashless                                             ' Using the Variable in the code
 

Forum statistics

Threads
1,186,129
Messages
5,956,064
Members
438,231
Latest member
fuzdrum

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top