VBA Filter data and copy to worksheets

[Jemma Atkinson]

Hi,

I want to filter for high value items in Sheet Raw Data which meet the below criteria, if criteria met then copy data to the criteria worksheets


High value criteria

>=1,000,000 <5,000,000 then copy to sheet >=1M<5M
>=5,000,000 <10,000,000 then copy to sheet >=5M<10M
>=10,000,000 then copy to sheet >=10M

See below example

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Group	Category	Set ID	Value Date	Entry Date	Type	Amount	AUD Equivalent	CCY	Age	Source	Ref1
2	TEST	TEST	TEST	21-Jan-12	23-Jan-12	LCR	1,000,000.00	1,000,000.00	AUD	2	TEST	TEST
3	TEST	TEST	TEST	24-Jan-12	24-Jan-12	LCR	-3,500,000.00	-3,500,000.00	AUD	1	TEST	TEST
4	TEST	TEST	TEST	23-Jan-12	23-Jan-12	SDR	5,600,000.00	5,600,000.00	AUD	2	TEST	TEST
5	TEST	TEST	TEST	24-Jan-12	24-Jan-12	SDR	-8,900,000.00	-8,900,000.00	AUD	1	TEST	TEST
6	TEST	TEST	TEST	15-Dec-11	15-Dec-11	LCR	-9,600,000.00	-9,537,529.18	USD	41	TEST	TEST
7	TEST	TEST	TEST	23-Jan-12	23-Jan-12	SCR	150,000,000.00	150,000,000.00	AUD	2	TEST	TEST
8	TEST	TEST	TEST	23-Jan-12	23-Jan-12	SCR	-11,000,000.00	-11,000,000.00	AUD	2	TEST	TEST
9	TEST	TEST	TEST	24-Jan-12	24-Jan-12	SCR	10,000,000.00	10,000,000.00	AUD	0	TEST	TEST
10	TEST	TEST	TEST	24-Jan-12	24-Jan-12	SCR	2,154,236.00	2,154,236.00	AUD	1	TEST	TEST
11	TEST	TEST	TEST	24-Jan-12	24-Jan-12	SCR	-6,456,212.00	-6,456,212.00	AUD	1	TEST	TEST
12	TEST	TEST	TEST	24-Jan-12	24-Jan-12	SCR	100,000.00	100,000.00	AUD	1	TEST	TEST
RawData

<b>>=1M<5M</b><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:45px;" /><col style="width:62px;" /><col style="width:43px;" /><col style="width:75px;" /><col style="width:71px;" /><col style="width:37px;" /><col style="width:87px;" /><col style="width:104px;" /><col style="width:34px;" /><col style="width:31px;" /><col style="width:49px;" /><col style="width:35px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Group</td><td >Category</td><td >Set ID</td><td >Value Date</td><td >Entry Date</td><td >Type</td><td >Amount</td><td >AUD Equivalent</td><td >CCY</td><td >Age</td><td >Source</td><td >Ref1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >TEST</td><td >TEST</td><td >TEST</td><td style="text-align:right; ">21-Jan-12</td><td style="text-align:right; ">23-Jan-12</td><td >LCR</td><td style="text-align:right; ">1,000,000.00</td><td style="text-align:right; ">1,000,000.00</td><td >AUD</td><td style="text-align:right; ">2</td><td >TEST</td><td >TEST</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >TEST</td><td >TEST</td><td >TEST</td><td style="text-align:right; ">24-Jan-12</td><td style="text-align:right; ">24-Jan-12</td><td >SCR</td><td style="text-align:right; ">2,154,236.00</td><td style="text-align:right; ">2,154,236.00</td><td >AUD</td><td style="text-align:right; ">1</td><td >TEST</td><td >TEST</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >TEST</td><td >TEST</td><td >TEST</td><td style="text-align:right; ">24-Jan-12</td><td style="text-align:right; ">24-Jan-12</td><td >LCR</td><td style="color:#ff0000; text-align:right; ">-3,500,000.00</td><td style="color:#ff0000; text-align:right; ">-3,500,000.00</td><td >AUD</td><td style="text-align:right; ">1</td><td >TEST</td><td >TEST</td></tr></table> <span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4 </a>

<b>>=5M<10M</b><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:45px;" /><col style="width:62px;" /><col style="width:43px;" /><col style="width:75px;" /><col style="width:71px;" /><col style="width:37px;" /><col style="width:87px;" /><col style="width:104px;" /><col style="width:34px;" /><col style="width:31px;" /><col style="width:49px;" /><col style="width:35px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Group</td><td >Category</td><td >Set ID</td><td >Value Date</td><td >Entry Date</td><td >Type</td><td >Amount</td><td >AUD Equivalent</td><td >CCY</td><td >Age</td><td >Source</td><td >Ref1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >TEST</td><td >TEST</td><td >TEST</td><td style="text-align:right; ">23-Jan-12</td><td style="text-align:right; ">23-Jan-12</td><td >SDR</td><td style="text-align:right; ">5,600,000.00</td><td style="text-align:right; ">5,600,000.00</td><td >AUD</td><td style="text-align:right; ">2</td><td >TEST</td><td >TEST</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >TEST</td><td >TEST</td><td >TEST</td><td style="text-align:right; ">24-Jan-12</td><td style="text-align:right; ">24-Jan-12</td><td >SDR</td><td style="color:#ff0000; text-align:right; ">-8,900,000.00</td><td style="color:#ff0000; text-align:right; ">-8,900,000.00</td><td >AUD</td><td style="text-align:right; ">1</td><td >TEST</td><td >TEST</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >TEST</td><td >TEST</td><td >TEST</td><td style="text-align:right; ">15-Dec-11</td><td style="text-align:right; ">15-Dec-11</td><td >LCR</td><td style="color:#ff0000; text-align:right; ">-9,600,000.00</td><td style="color:#ff0000; text-align:right; ">-9,537,529.18</td><td >USD</td><td style="text-align:right; ">41</td><td >TEST</td><td >TEST</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >TEST</td><td >TEST</td><td >TEST</td><td style="text-align:right; ">24-Jan-12</td><td style="text-align:right; ">24-Jan-12</td><td >SCR</td><td style="color:#ff0000; text-align:right; ">-6,456,212.00</td><td style="color:#ff0000; text-align:right; ">-6,456,212.00</td><td >AUD</td><td style="text-align:right; ">1</td><td >TEST</td><td >TEST</td></tr></table> <span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4 </a>

<b>>=10M</b><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:45px;" /><col style="width:62px;" /><col style="width:43px;" /><col style="width:75px;" /><col style="width:71px;" /><col style="width:37px;" /><col style="width:97px;" /><col style="width:104px;" /><col style="width:34px;" /><col style="width:31px;" /><col style="width:49px;" /><col style="width:35px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Group</td><td >Category</td><td >Set ID</td><td >Value Date</td><td >Entry Date</td><td >Type</td><td >Amount</td><td >AUD Equivalent</td><td >CCY</td><td >Age</td><td >Source</td><td >Ref1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >TEST</td><td >TEST</td><td >TEST</td><td style="text-align:right; ">23-Jan-12</td><td style="text-align:right; ">23-Jan-12</td><td >SCR</td><td style="text-align:right; ">150,000,000.00</td><td style="text-align:right; ">150,000,000.00</td><td >AUD</td><td style="text-align:right; ">2</td><td >TEST</td><td >TEST</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >TEST</td><td >TEST</td><td >TEST</td><td style="text-align:right; ">23-Jan-12</td><td style="text-align:right; ">23-Jan-12</td><td >SCR</td><td style="color:#ff0000; text-align:right; ">-11,000,000.00</td><td style="color:#ff0000; text-align:right; ">-11,000,000.00</td><td >AUD</td><td style="text-align:right; ">2</td><td >TEST</td><td >TEST</td></tr></table> <span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4 </a>

 

[wigi]

Hi Jemma

Topics like this have been covered before.
Various websites list the possibilities, including an Advanced Filter:
Extract data using Advanced Filter and VBA | Chandoo.org - Learn Microsoft Excel Online

 

[Jemma Atkinson]

Ok, I am trying to to do a manual test by using advanced filter for >=1m<5m, what is wrong with my advanced filter criteria? When i use this it only filters for >0 but not the amounts

Excel Workbook
	Q	R
1	AUD Equivalent	Age
2	>=1000000	>0
3		>0
4		>0
5	>-5000000	>0
RawData

 

[wigi]

The ranges are correct?
That is, you did not specify your criteria range too small?
The cells for the criteria are formatted as Text ?

 

[dmt32]

Hi,
Have a look here:http://www.contextures.com/xladvfilter01.html#CriteriaRange
gives step by step guide which may find helpful

Dave

 

[Jemma Atkinson]

The ranges are correct?
That is, you did not specify your criteria range too small?
The cells for the criteria are formatted as Text ?

Yes the rng is correct, I have used named range "RngCriteria1"

The cells for the criteria are not formatted as text

 

[MARK858]

Jemma, it is not if it is formatted as text but if the underlying value is text. Try putting =ISNUMBER(D1) where D1 is a cell in question and see if it gives true or false

 

[Jemma Atkinson]

Jemma, it is not if it is formatted as text but if the underlying value is text. Try putting =ISNUMBER(D1) where D1 is a cell in question and see if it gives true or false

ok i got the filter working manually, but when i try to convert to code the code gives me error on the advanced filter part

Sub Macro1()

Dim ShtRaw As Worksheet, Sht1M5M As Worksheet, Sht5M10M As Worksheet, Sht10M As Worksheet
Dim lng As Long

Set ShtRaw = Sheets("RawData")
Set Sht1M5M = Sheets(">=1M<5M")
Set Sht5M10 = Sheets(">=5M<10M")
Set Sht10M = Sheets(">=10M")



With ShtRaw
lng = .Range("A" & Rows.Count).End(xlUp).Row
.Columns("H").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
With .Range("H2:H" & lng)
.FormulaR1C1 = "=RC[-1]/INDEX(rngRate,MATCH(RC[1],rngCcy,0))"
.Value = .Value
End With
With.Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("rngCriteria1"), CopyToRange:=Range("A1:L1"), Unique _
:=False
End With
End With

End Sub

 

[MARK858]

On my phone so can't post any code but try changing it from current region to the range you are looking up the data in.
Edit: By range I do mean within a single column and copy to a single cell

 

[Jemma Atkinson]

Ok i have got the code working for the >=1m <5M, now i assume i have to repeat the code 3 times to get to my result, unless there is a quicker way?

Sub Macro1()

    Dim ShtRaw As Excel.Worksheet, Sht1M5M As Excel.Worksheet, Sht5M10M As Excel.Worksheet, Sht10M As Excel.Worksheet
    Dim lng As Long
    Dim rngToCopy As Range
    Dim ShtTemp As Excel.Worksheet
    
    Set ShtRaw = ThisWorkbook.Sheets("RawData")
    Set Sht1M5M = ThisWorkbook.Sheets(">=1M<5M")
    Set Sht5M10 = ThisWorkbook.Sheets(">=5M<10M")
    Set Sht10M = ThisWorkbook.Sheets(">=10M")

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    
    
    With ShtRaw
        .AutoFilterMode = False
         lng = .Range("A" & Rows.Count).End(xlUp).Row
        .Columns("H").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        With .Range("H2:H" & lng)
            .FormulaR1C1 = "=RC[-1]/INDEX(rngRate,MATCH(RC[1],rngCcy,0))"
            .Value = .Value
        End With
        .Range("H1").Value = "AUD Equivalent"
        Set ShtTemp = Sheets.Add
        With ShtTemp
            ShtRaw.AutoFilterMode = False
            ShtRaw.Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=Range("rngCriteria1"), CopyToRange:=.Range("A1"), Unique:=False
            Set rngToCopy = .Range("A1").CurrentRegion.Offset(1, 0)
            rngToCopy.Copy Sht1M5M.Range("A2")
            ShtTemp.Delete
        End With
    End With
 
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
End Sub