VBA Filter data and copy to worksheets

Jemma Atkinson · Oct 5, 2013

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

21-Jan-12

23-Jan-12

LCR

1,000,000.00

AUD

2

TEST

3

TEST

24-Jan-12

LCR

-3,500,000.00

AUD

1

TEST

4

TEST

23-Jan-12

SDR

5,600,000.00

AUD

2

TEST

5

TEST

24-Jan-12

SDR

-8,900,000.00

AUD

1

TEST

6

TEST

15-Dec-11

LCR

-9,600,000.00

-9,537,529.18

USD

41

TEST

7

TEST

23-Jan-12

SCR

150,000,000.00

AUD

2

TEST

8

TEST

23-Jan-12

SCR

-11,000,000.00

AUD

2

TEST

9

TEST

24-Jan-12

SCR

10,000,000.00

AUD

0

TEST

10

TEST

24-Jan-12

SCR

2,154,236.00

AUD

1

TEST

11

TEST

24-Jan-12

SCR

-6,456,212.00

AUD

1

TEST

12

TEST

24-Jan-12

SCR

100,000.00

AUD

1

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>

markmzz · Oct 6, 2013

Here is the code (test with a copy of your workbook) :

Code:

Option Explicit

Sub AdvFilter()

    Dim shRaw As Worksheet, sh1M5M As Worksheet
    Dim sh5M10M As Worksheet, sh10M As Worksheet
    Dim LastRow As Long
    Dim RngToCopy As String, RngCriteria As String, RngData As String
    
    Set shRaw = ThisWorkbook.Sheets("RawData")
    Set sh1M5M = ThisWorkbook.Sheets(">=1M<5M")
    Set sh5M10M = ThisWorkbook.Sheets(">=5M<10M")
    Set sh10M = ThisWorkbook.Sheets(">=10M")

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    
    With shRaw
        .AutoFilterMode = False
        LastRow = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("N1").Value = "CalcCriteria"
        RngData = "A1:L" & LastRow
        RngCriteria = "N1:N2"
       
        'sh1M5M =============================================================
        .Range("N2").Formula = "=AND(ABS(H2)>=1000000,ABS(H2)<5000000,J2>0)"
        sh1M5M.Cells.Clear
        RngToCopy = "A1"
        .Range(RngData).AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=.Range(RngCriteria), CopyToRange:=sh1M5M.Range(RngToCopy)
        
        'sh5M10M ============================================================
        .Range("N2").Formula = "=AND(ABS(H2)>=5000000,ABS(H2)<10000000,J2>0)"
        sh5M10M.Cells.Clear
        RngToCopy = "A1"
        .Range(RngData).AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=.Range(RngCriteria), CopyToRange:=sh5M10M.Range(RngToCopy)
        
        'sh10M ==============================================================
        .Range("N2").Formula = "=AND(ABS(H2)>=10000000,J2>0)"
        sh10M.Cells.Clear
        RngToCopy = "A1"
        .Range(RngData).AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=.Range(RngCriteria), CopyToRange:=sh10M.Range(RngToCopy)
    End With
 
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
End Sub

Layout

[TABLE="width: 498"]
<tbody>[TR]
[TD="class: xl69, width: 27, bgcolor: #D9D9D9"][/TD]
[TD="class: xl69, width: 37, bgcolor: #D9D9D9"]A
[/TD]
[TD="class: xl69, width: 49, bgcolor: #D9D9D9"]B
[/TD]
[TD="class: xl69, width: 34, bgcolor: #D9D9D9"]C
[/TD]
[TD="class: xl69, width: 59, bgcolor: #D9D9D9"]D
[/TD]
[TD="class: xl69, width: 57, bgcolor: #D9D9D9"]E
[/TD]
[TD="class: xl69, width: 32, bgcolor: #D9D9D9"]F
[/TD]
[TD="class: xl69, width: 82, bgcolor: #D9D9D9"]G
[/TD]
[TD="class: xl69, width: 82, bgcolor: #D9D9D9"]H
[/TD]
[TD="class: xl69, width: 27, bgcolor: #D9D9D9"]I
[/TD]
[TD="class: xl69, width: 27, bgcolor: #D9D9D9"]J
[/TD]
[TD="class: xl69, width: 39, bgcolor: #D9D9D9"]K
[/TD]
[TD="class: xl69, width: 28, bgcolor: #D9D9D9"]L
[/TD]
[TD="class: xl69, width: 17, bgcolor: #D9D9D9"]M
[/TD]
[TD="class: xl69, width: 65, bgcolor: #D9D9D9"]N
[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: #D9D9D9"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]Group
[/TD]
[TD="class: xl65, bgcolor: transparent"]Category
[/TD]
[TD="class: xl65, bgcolor: transparent"]Set ID
[/TD]
[TD="class: xl65, bgcolor: transparent"]Value Date
[/TD]
[TD="class: xl65, bgcolor: transparent"]Entry Date
[/TD]
[TD="class: xl65, bgcolor: transparent"]Type
[/TD]
[TD="class: xl65, bgcolor: transparent"]Amount
[/TD]
[TD="class: xl65, bgcolor: transparent"]AUD Equivalent
[/TD]
[TD="class: xl65, bgcolor: transparent"]CCY
[/TD]
[TD="class: xl65, bgcolor: transparent"]Age
[/TD]
[TD="class: xl65, bgcolor: transparent"]Source
[/TD]
[TD="class: xl65, bgcolor: transparent"]Ref1
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]CalcCriteria
[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: #D9D9D9"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]21/jan/12
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]23/jan/12
[/TD]
[TD="class: xl65, bgcolor: transparent"]LCR
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1.000.000,00
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1.000.000,00
[/TD]
[TD="class: xl65, bgcolor: transparent"]AUD
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: yellow, align: center"]VERDADEIRO
[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: #D9D9D9"]3
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]24/jan/12
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]24/jan/12
[/TD]
[TD="class: xl65, bgcolor: transparent"]LCR
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]-3.500.000,00
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]-3.500.000,00
[/TD]
[TD="class: xl65, bgcolor: transparent"]AUD
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: #D9D9D9"]4
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]23/jan/12
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]23/jan/12
[/TD]
[TD="class: xl65, bgcolor: transparent"]SDR
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]5.600.000,00
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]5.600.000,00
[/TD]
[TD="class: xl65, bgcolor: transparent"]AUD
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: #D9D9D9"]5
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]24/jan/12
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]24/jan/12
[/TD]
[TD="class: xl65, bgcolor: transparent"]SDR
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]-8.900.000,00
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]-8.900.000,00
[/TD]
[TD="class: xl65, bgcolor: transparent"]AUD
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: #D9D9D9"]6
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]15-Dec-11
[/TD]
[TD="class: xl65, bgcolor: transparent"]15-Dec-11
[/TD]
[TD="class: xl65, bgcolor: transparent"]LCR
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]-9.600.000,00
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]-9.537.529,18
[/TD]
[TD="class: xl65, bgcolor: transparent"]USD
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]41
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: #D9D9D9"]7
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]23/jan/12
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]23/jan/12
[/TD]
[TD="class: xl65, bgcolor: transparent"]SCR
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]150.000.000,00
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]150.000.000,00
[/TD]
[TD="class: xl65, bgcolor: transparent"]AUD
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: #D9D9D9"]8
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]23/jan/12
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]23/jan/12
[/TD]
[TD="class: xl65, bgcolor: transparent"]SCR
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]-11.000.000,00
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]-11.000.000,00
[/TD]
[TD="class: xl65, bgcolor: transparent"]AUD
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: #D9D9D9"]9
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]24/jan/12
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]24/jan/12
[/TD]
[TD="class: xl65, bgcolor: transparent"]SCR
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]10.000.000,00
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]10.000.000,00
[/TD]
[TD="class: xl65, bgcolor: transparent"]AUD
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: #D9D9D9"]10
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]24/jan/12
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]24/jan/12
[/TD]
[TD="class: xl65, bgcolor: transparent"]SCR
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2.154.236,00
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2.154.236,00
[/TD]
[TD="class: xl65, bgcolor: transparent"]AUD
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: #D9D9D9"]11
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]24/jan/12
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]24/jan/12
[/TD]
[TD="class: xl65, bgcolor: transparent"]SCR
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]-6.456.212,00
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]-6.456.212,00
[/TD]
[TD="class: xl65, bgcolor: transparent"]AUD
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: #D9D9D9"]12
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]24/jan/12
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]24/jan/12
[/TD]
[TD="class: xl65, bgcolor: transparent"]SCR
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]100.000,00
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]100.000,00
[/TD]
[TD="class: xl65, bgcolor: transparent"]AUD
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"]TEST
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]****
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]********
[/TD]
[TD="class: xl65, bgcolor: transparent"]*****
[/TD]
[TD="class: xl65, bgcolor: transparent"]**********
[/TD]
[TD="class: xl65, bgcolor: transparent"]**********
[/TD]
[TD="class: xl65, bgcolor: transparent"]*****
[/TD]
[TD="class: xl65, bgcolor: transparent"]************
[/TD]
[TD="class: xl65, bgcolor: transparent"]**************
[/TD]
[TD="class: xl65, bgcolor: transparent"]****
[/TD]
[TD="class: xl65, bgcolor: transparent"]****
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]****
[/TD]
[TD="class: xl65, bgcolor: transparent"]**
[/TD]
[TD="class: xl65, bgcolor: transparent"]************
[/TD]
[/TR]
</tbody>[/TABLE]

Markmzz

markmzz · Oct 6, 2013

A small modification in my code (test with a copy of your workbook) :

Code:

Option Explicit

Sub AdvFilter()

    Dim shRaw As Worksheet
    Dim mySheet As Worksheet
    Dim LastRow As Long, i As Integer
    Dim RngToCopy As String, RngCriteria As String, RngData As String
    Dim mySheets As Variant, myRange As Variant
    
    Set shRaw = ThisWorkbook.Sheets("RawData")
    mySheets = Array(">=1M<5M", ">=5M<10M", ">=10M")
    myRange = Array(1000000, 5000000, 10000000, 0)
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    
    With shRaw
        .AutoFilterMode = False
        LastRow = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("N1").Value = "CalcCriteria"
        RngData = "A1:L" & LastRow
        RngCriteria = "N1:N2"
       
        For i = 0 To 2
            Set mySheet = ThisWorkbook.Sheets(mySheets(i))
            'mySheet =============================================================
            .Range("N2").Formula = "=AND(ABS(H2)>=" & myRange(i) & _
            IIf(i < 2, ",ABS(H2)<" & myRange(i + 1), "") & ",J2>0)"
            mySheet.Cells.Clear
            RngToCopy = "A1"
            .Range(RngData).AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=.Range(RngCriteria), CopyToRange:=mySheet.Range(RngToCopy)
        Next i
    End With
 
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
End Sub

Markmzz

VBA Filter data and copy to worksheets

Jemma Atkinson

Well-known Member

markmzz

MrExcel MVP

markmzz

MrExcel MVP

Similar threads

Share this page

VBA Filter data and copy to worksheets

Jemma Atkinson

Well-known Member

markmzz

MrExcel MVP

markmzz

MrExcel MVP

Similar threads

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