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

joach · Jul 19, 2022

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 year	if todays date is between 20 months and 24 months from the purchase date
5 year	if 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.

joach · Jul 20, 2022

Alex Blakenburg · Jul 21, 2022

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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

Col1

Col2

Col3

Col4

Col5

Purchase Date

Col7

Col8

Col9

Mths

Follow Up

Advanced Filter Criteria

2

7/05/2015

86

No

FALSE

3

7/06/2015

85

No

4

7/07/2015

84

No

Set today as

20/07/2022

5

7/08/2015

83

No

6

7/09/2015

82

No

7

7/10/2015

81

No

8

7/11/2015

80

No

9

7/12/2015

79

No

10

7/01/2016

78

No

11

7/02/2016

77

No

12

7/03/2016

76

No

13

7/04/2016

75

No

14

7/05/2016

74

No

15

7/06/2016

73

No

16

7/07/2016

72

No

17

7/08/2016

71

No

18

7/09/2016

70

No

19

7/10/2016

69

No

20

7/11/2016

68

No

21

7/12/2016

67

No

22

7/01/2017

66

No

23

7/02/2017

65

No

24

7/03/2017

64

No

25

7/04/2017

63

No

26

7/05/2017

62

No

27

7/06/2017

61

No

28

7/07/2017

60

No

29

7/08/2017

59

Yes

30

7/09/2017

58

Yes

31

7/10/2017

57

Yes

32

7/11/2017

56

Yes

33

7/12/2017

55

No

34

7/01/2018

54

No

Sheet1

Cell Formulas
Range	Formula
J2:J34	J2	=DATEDIF(F2,$N$4,"m")
K2:K34	K2	=IF(AND(OR(INT(J2/12)=0,INT(J2/12)=1,INT(J2/12)=4),MOD(J2,12)>=8),"Yes","No")
M2	M2	=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)

joach · Jul 21, 2022

Alex Blakenburg said:
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
A B C D E F G H I J K L M N
1 Col1 Col2 Col3 Col4 Col5 Purchase Date Col7 Col8 Col9 Mths Follow Up Advanced Filter Criteria
2 7/05/2015 86 No FALSE
3 7/06/2015 85 No
4 7/07/2015 84 No Set today as 20/07/2022
5 7/08/2015 83 No
6 7/09/2015 82 No
7 7/10/2015 81 No
8 7/11/2015 80 No
9 7/12/2015 79 No
10 7/01/2016 78 No
11 7/02/2016 77 No
12 7/03/2016 76 No
13 7/04/2016 75 No
14 7/05/2016 74 No
15 7/06/2016 73 No
16 7/07/2016 72 No
17 7/08/2016 71 No
18 7/09/2016 70 No
19 7/10/2016 69 No
20 7/11/2016 68 No
21 7/12/2016 67 No
22 7/01/2017 66 No
23 7/02/2017 65 No
24 7/03/2017 64 No
25 7/04/2017 63 No
26 7/05/2017 62 No
27 7/06/2017 61 No
28 7/07/2017 60 No
29 7/08/2017 59 Yes
30 7/09/2017 58 Yes
31 7/10/2017 57 Yes
32 7/11/2017 56 Yes
33 7/12/2017 55 No
34 7/01/2018 54 No
Sheet1
Cell Formulas
Range Formula
J2:J34 J2 =DATEDIF(F2,$N$4,"m")
K2:K34 K2 =IF(AND(OR(INT(J2/12)=0,INT(J2/12)=1,INT(J2/12)=4),MOD(J2,12)>=8),"Yes","No")
M2 M2 =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

A

B

C

D

E

F

G

H

I

J

K

1

Order Number

Address

Street

Suburb

Description

Estimated cost

Lodged date

Customer name

CSO

purchase type

Purchase date

2

1

A

XXXXXX

$0.00

xx/xx/xx

XXXX

YYYYY

Cash

21/04/2021

3

2

B

XXXXXX

$100.00

xx/xx/xx

XXXX

YYYYY

paypal

10/09/2019

4

3

C

XXXXXX

$200.00

xx/xx/xx

XXXX

YYYYY

Credit Card

14/10/2019

5

4

A

XXXXXX

$300.00

xx/xx/xx

XXXX

YYYYY

debit card

23/10/2018

6

5

B

XXXXXX

$400.00

xx/xx/xx

XXXX

YYYYY

Cash

26/03/2019

7

6

C

XXXXXX

$500.00

xx/xx/xx

XXXX

YYYYY

paypal

26/03/2019

8

7

A

XXXXXX

$600.00

xx/xx/xx

XXXX

YYYYY

Credit Card

2/08/2018

9

8

B

XXXXXX

$700.00

xx/xx/xx

XXXX

YYYYY

debit card

22/08/2019

10

9

C

XXXXXX

$800.00

xx/xx/xx

XXXX

YYYYY

debit card

20/09/2017

11

10

A

XXXXXX

$900.00

xx/xx/xx

XXXX

YYYYY

paypal

7/05/2019

12

11

B

XXXXXX

$1,000.00

xx/xx/xx

XXXX

YYYYY

Credit Card

7/05/2019

13

12

C

XXXXXX

$1,100.00

xx/xx/xx

XXXX

YYYYY

debit card

8/05/2018

14

13

A

XXXXXX

$1,200.00

xx/xx/xx

XXXX

YYYYY

Cash

8/05/2018

15

14

B

XXXXXX

$1,300.00

xx/xx/xx

XXXX

YYYYY

paypal

30/06/2020

16

15

C

XXXXXX

$1,400.00

xx/xx/xx

XXXX

YYYYY

Credit Card

9/03/2018

17

16

A

XXXXXX

$1,500.00

xx/xx/xx

XXXX

YYYYY

debit card

2/04/2019

Sheet7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K2:K1794	Expression	=" =EDATE(today(),-5)"	text	NO
A2:K9315	Cell Value	contains "2.00"	text	NO
A2:K9315	Cell Value	contains "12.00"	text	NO
A2:K9315	Cell Value	contains "trial period"	text	NO
A2:K9315	Cell Value	contains "0pm"	text	NO
A2:K9315	Cell Value	contains "0am"	text	NO
A2:K9315	Cell Value	contains "midnight"	text	NO
A2:K9315	Cell Value	contains "0 am"	text	NO
A2:K9315	Cell Value	contains "0 pm"	text	NO
A2:K9315	Cell Value	contains "operation"	text	NO
A2:K9315	Cell Value	contains "small bar"	text	NO
A2:K9315	Cell Value	contains "food"	text	NO
A2:K9315	Cell Value	contains "hour"	text	NO

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]

joach · Jul 21, 2022

Alex Blakenburg said:
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
A B C D E F G H I J K L M N
1 Col1 Col2 Col3 Col4 Col5 Purchase Date Col7 Col8 Col9 Mths Follow Up Advanced Filter Criteria
2 7/05/2015 86 No FALSE
3 7/06/2015 85 No
4 7/07/2015 84 No Set today as 20/07/2022
5 7/08/2015 83 No
6 7/09/2015 82 No
7 7/10/2015 81 No
8 7/11/2015 80 No
9 7/12/2015 79 No
10 7/01/2016 78 No
11 7/02/2016 77 No
12 7/03/2016 76 No
13 7/04/2016 75 No
14 7/05/2016 74 No
15 7/06/2016 73 No
16 7/07/2016 72 No
17 7/08/2016 71 No
18 7/09/2016 70 No
19 7/10/2016 69 No
20 7/11/2016 68 No
21 7/12/2016 67 No
22 7/01/2017 66 No
23 7/02/2017 65 No
24 7/03/2017 64 No
25 7/04/2017 63 No
26 7/05/2017 62 No
27 7/06/2017 61 No
28 7/07/2017 60 No
29 7/08/2017 59 Yes
30 7/09/2017 58 Yes
31 7/10/2017 57 Yes
32 7/11/2017 56 Yes
33 7/12/2017 55 No
34 7/01/2018 54 No
Sheet1
Cell Formulas
Range Formula
J2:J34 J2 =DATEDIF(F2,$N$4,"m")
K2:K34 K2 =IF(AND(OR(INT(J2/12)=0,INT(J2/12)=1,INT(J2/12)=4),MOD(J2,12)>=8),"Yes","No")
M2 M2 =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)

joach said:
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
A B C D E F G H I J K
1 Order Number Address Street Suburb Description Estimated cost Lodged date Customer name CSO purchase type Purchase date
2 1 A A XXXXXX $0.00 xx/xx/xx XXXX YYYYY Cash 21/04/2021
3 2 B B XXXXXX $100.00 xx/xx/xx XXXX YYYYY paypal 10/09/2019
4 3 C C XXXXXX $200.00 xx/xx/xx XXXX YYYYY Credit Card 14/10/2019
5 4 A A XXXXXX $300.00 xx/xx/xx XXXX YYYYY debit card 23/10/2018
6 5 B B XXXXXX $400.00 xx/xx/xx XXXX YYYYY Cash 26/03/2019
7 6 C C XXXXXX $500.00 xx/xx/xx XXXX YYYYY paypal 26/03/2019
8 7 A A XXXXXX $600.00 xx/xx/xx XXXX YYYYY Credit Card 2/08/2018
9 8 B B XXXXXX $700.00 xx/xx/xx XXXX YYYYY debit card 22/08/2019
10 9 C C XXXXXX $800.00 xx/xx/xx XXXX YYYYY debit card 20/09/2017
11 10 A A XXXXXX $900.00 xx/xx/xx XXXX YYYYY paypal 7/05/2019
12 11 B B XXXXXX $1,000.00 xx/xx/xx XXXX YYYYY Credit Card 7/05/2019
13 12 C C XXXXXX $1,100.00 xx/xx/xx XXXX YYYYY debit card 8/05/2018
14 13 A A XXXXXX $1,200.00 xx/xx/xx XXXX YYYYY Cash 8/05/2018
15 14 B B XXXXXX $1,300.00 xx/xx/xx XXXX YYYYY paypal 30/06/2020
16 15 C C XXXXXX $1,400.00 xx/xx/xx XXXX YYYYY Credit Card 9/03/2018
17 16 A A XXXXXX $1,500.00 xx/xx/xx XXXX YYYYY debit card 2/04/2019
Sheet7
Cells with Conditional Formatting
Cell Condition Cell Format Stop If True
K2:K1794 Expression =" =EDATE(today(),-5)" text NO
A2:K9315 Cell Value contains "2.00" text NO
A2:K9315 Cell Value contains "12.00" text NO
A2:K9315 Cell Value contains "trial period" text NO
A2:K9315 Cell Value contains "0pm" text NO
A2:K9315 Cell Value contains "0am" text NO
A2:K9315 Cell Value contains "midnight" text NO
A2:K9315 Cell Value contains "0 am" text NO
A2:K9315 Cell Value contains "0 pm" text NO
A2:K9315 Cell Value contains "operation" text NO
A2:K9315 Cell Value contains "small bar" text NO
A2:K9315 Cell Value contains "food" text NO
A2:K9315 Cell Value contains "hour" text NO

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 · Jul 21, 2022

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.

joach · Jul 22, 2022

Alex Blakenburg said:
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 · Jul 22, 2022

Give the code below a try. You will need to put it into a standard / regular VBA module.

joach said:
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

joach · Jul 23, 2022

Alex Blakenburg said:

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.

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 · Jul 24, 2022

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

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

joach

New Member

Excel Facts

joach

New Member

Alex Blakenburg

MrExcel MVP

joach

New Member

joach

New Member

Alex Blakenburg

MrExcel MVP

joach

New Member

Alex Blakenburg

MrExcel MVP

joach

New Member

Alex Blakenburg

MrExcel MVP

Similar threads

Forum statistics

Share this page

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

New Member

Excel Facts

New Member

MrExcel MVP

New Member

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock