VBA Filter data and copy to worksheets

Jemma Atkinson

Jemma Atkinson

Well-known Member
Joined
Jul 7, 2008
Messages
509
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
ABCDEFGHIJKL
1GroupCategorySet IDValue DateEntry DateTypeAmountAUD EquivalentCCYAgeSourceRef1
2TESTTESTTEST21-Jan-1223-Jan-12LCR1,000,000.001,000,000.00AUD2TESTTEST
3TESTTESTTEST24-Jan-1224-Jan-12LCR-3,500,000.00-3,500,000.00AUD1TESTTEST
4TESTTESTTEST23-Jan-1223-Jan-12SDR5,600,000.005,600,000.00AUD2TESTTEST
5TESTTESTTEST24-Jan-1224-Jan-12SDR-8,900,000.00-8,900,000.00AUD1TESTTEST
6TESTTESTTEST15-Dec-1115-Dec-11LCR-9,600,000.00-9,537,529.18USD41TESTTEST
7TESTTESTTEST23-Jan-1223-Jan-12SCR150,000,000.00150,000,000.00AUD2TESTTEST
8TESTTESTTEST23-Jan-1223-Jan-12SCR-11,000,000.00-11,000,000.00AUD2TESTTEST
9TESTTESTTEST24-Jan-1224-Jan-12SCR10,000,000.0010,000,000.00AUD0TESTTEST
10TESTTESTTEST24-Jan-1224-Jan-12SCR2,154,236.002,154,236.00AUD1TESTTEST
11TESTTESTTEST24-Jan-1224-Jan-12SCR-6,456,212.00-6,456,212.00AUD1TESTTEST
12TESTTESTTEST24-Jan-1224-Jan-12SCR100,000.00100,000.00AUD1TESTTEST
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>
 
Sort by date Sort by votes
See if this does what you want. It looks bit busy but hopefully will work for you. Suggest back your workbook up before testing & you may need to tweek as required.

It Should if all works, filter according to your criteria to named worksheets - if they do not exist, they will be created.

Copy all code to standard module.

Code: 
Option Explicit
Sub FilterData()
    Dim wsData As Worksheet, wsNames As Worksheet, wsFilter As Worksheet
    Dim Datarng As Range
    Dim rowcount As Long
    Dim colcount As Integer, msg As Integer
    Dim i As Integer, shindex As Integer
    
    Dim NewSh As Boolean
    Dim arr As Variant, ShName As Variant
    
    On Error GoTo progend
    'your master sheet
    Set wsData = Worksheets("RawData")
    'criteria values
    arr = Array(">=1,000,000", "<5,000,000", _
                ">=5,000,000", "<10,000,000", _
                ">=10,000,000", "")
     
     'sheet names to copy data to
    ShName = Array(">=1M<5M", ">=5M<10M", ">=10M")
    'turn off events / alerts / calc / Screenupdating
    EventsEnable False
    'add filter sheet
    Set wsFilter = Worxsheet("Filter")
    With wsData
        .Activate
        .Unprotect Password:=""  'add password if needed
        
        'set the data range
        rowcount = .Cells(.Rows.Count, 1).End(xlUp).Row
        colcount = .Cells(1, .Columns.Count).End(xlToLeft).Column
        Set Datarng = .Range(.Cells(1, 1), .Cells(rowcount, colcount))
        
        'add criteria headings to filter sheet
        wsFilter.Range("A1:B1").Value = wsData.Range("H1").Value
        
        shindex = 0
        For i = LBound(arr) To UBound(arr) Step 2
            'add the Filter values to criteria
            With wsFilter
                .Range("A2").Value = arr(i)
                .Range("B2").Value = arr(i + 1)
            End With
            
            Set wsNames = Worxsheet(ShName(shindex), NewSh)
            'clear existing sheet data
            If Not NewSh Then wsNames.Cells.ClearContents
            Datarng.AdvancedFilter Action:=xlFilterCopy, _
                                   CriteriaRange:=wsFilter.Range("A1:B2"), _
                                   CopyToRange:=wsNames.Range("A1"), _
                                   Unique:=False
            
            shindex = shindex + 1
        Next
        .Select
    End With
progend:
    wsFilter.Delete
    EventsEnable True
    If Err > 0 Then
        msg = MsgBox(Error(Err), vbCritical, "Error")
        Err.Clear
    End If
End Sub

Function Worxsheet(ByVal sh As String, Optional ByRef NewSheet As Boolean = False) As Worksheet
    Dim ws As Worksheet
    On Error Resume Next
    With ThisWorkbook
        Set ws = .Worksheets(sh)
        If Err.Number = 9 Then
            Set ws = .Worksheets.Add
            ws.Move after:=.Worksheets(.Sheets.Count)
            ws.Name = sh
            NewSheet = True
        End If
    End With
    On Error GoTo 0
    Set Worxsheet = ws
End Function

Sub EventsEnable(State As Boolean)
    With Application
        .ScreenUpdating = State
        .EnableEvents = State
        .DisplayAlerts = State
        .Calculation = IIf(State, xlCalculationAutomatic, xlCalculationManual)
    End With
End Sub

Hope helpful

Dave
 
Upvote 0
Ok, the code is working for positive amounts but when i add negative amounts, i get an error message "Subscript out of range"

Code: 
   arr = Array(">=1,000,000", "<5,000,000", _
                ">=5,000,000", "<10,000,000", _
                ">=10,000,000", "", _
      [COLOR=#ff0000]          "<=-1,000,000", ">-5,000,000", _
                "<=-5,000,000", "<-10,000,000", _
                "<=-10,000,000", "")
[/COLOR]
 
Upvote 0
That's because the array arr is looped through, and in combination with (only) 3 sheet names stored in ShName. Change accordingly.
Also, hope that you spotted Dave's double typo: Set wsFilter = Worxsheet("Filter")
 
Upvote 0
Yes i spootted the worksheet error, but as for the 3 sheets they are correct, there are no further sheets to be created the (-) are part of each of those 3 sheets as shown in my example.
 
Upvote 0
In that case, as alluded to above, you need to change the For Next loop over the contents of the array arr.
The logic that Dave put forward, cannot cope with the negative amounts that you want for your criteria.
 
Upvote 0
wigi said:
Also, hope that you spotted Dave's double typo: Set wsFilter = Worxsheet("Filter")
Click to expand...

Set wsFilter = Worxsheet("Filter") is not a typo Worxsheet is a Function that is included with code I posted to manage creation of sheets if they do not exist.

Dave
 
Upvote 0
Oh, apologies, I did not see that. Maybe changing the function name would have been more clear :-)
 
Upvote 0
dmt32 said:
Set wsFilter = Worxsheet("Filter") is not a typo Worxsheet is a Function that is included with code I posted to manage creation of sheets if they do not exist.

Dave
Click to expand...

Thanks Dave, but I am still unsure how to treat the negative values, how do we change the For next loop?
 
Upvote 0
Jemma Atkinson said:
Thanks Dave, but I am still unsure how to treat the negative values, how do we change the For next loop?
Click to expand...

I have posted this code in many variants on this board in the past so quickly adapted it based on your original request as I thought it came close to what you were looking for. Code originally was designed to filter unique values and using the WorxSheet Function, create one worksheet for each value and copy relevant data to it. </SPAN>

To try and assist with your requirement, I added two arrays, one with the criteria values & other with your worksheets names as you had already created these. I retained the Worxsheet function as it was still used to create the filter sheet (which is deleted at end) and ensure that if any of sheets in shname array were missing, these would be created.</SPAN>

By Expanding the arr array with more values this will give subscript out of range error as you only have three worksheets in the shname array.</SPAN>

You can get around this by doing one of two things:</SPAN>

1 – add the worksheet names for the additional criteria values to the shname array</SPAN>

Or,</SPAN>

2 - use code as it was originally intended by changing this line:</SPAN>

Code: 
</SPAN>
Set wsNames = Worxsheet(ShName(shindex), NewSh)</SPAN>
</SPAN>

To this:</SPAN>

Code: 
</SPAN>
Set wsNames = Worxsheet(arr(i), NewSh)</SPAN>
</SPAN>

Ensure that you have copied the Worxsheet function code.</SPAN>

When run, code should create a worksheet with name based on every other arr value & copy relevant data to it – hopefully, output result will be close to what you are looking for but please be mindful that this was only ever intended to give some guidance to help you resolve your problem. You may need to develop / adapt it further to meet specific need.</SPAN>

Hope helpful</SPAN>

Dave</SPAN>
 
Upvote 0
Another way (with calculated criteria):

Criteria range ($N$1:$N$2)

Code: 
N2-> =AND(ABS(H2)>=1000000,ABS(H2)<5000000,J2>0)

PS: VERDADEIRO = TRUE

Layout

[TABLE="width: 498"]
<colgroup><col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 987;"> <col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;"> <col width="49" style="width: 37pt; mso-width-source: userset; mso-width-alt: 1792;"> <col width="34" style="width: 26pt; mso-width-source: userset; mso-width-alt: 1243;"> <col width="59" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2157;"> <col width="57" style="width: 43pt; mso-width-source: userset; mso-width-alt: 2084;"> <col width="32" style="width: 24pt; mso-width-source: userset; mso-width-alt: 1170;"> <col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;" span="2"> <col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 987;" span="2"> <col width="39" style="width: 29pt; mso-width-source: userset; mso-width-alt: 1426;"> <col width="28" style="width: 21pt; mso-width-source: userset; mso-width-alt: 1024;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="65" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2377;"> <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: xl69, bgcolor: #D9D9D9"]13[/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]
[TR]
[TD="class: xl69, bgcolor: #D9D9D9"]14[/TD]
[TD="class: xl65, bgcolor: transparent"]Result[/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]
[TR]
[TD="class: xl69, bgcolor: #D9D9D9"]15[/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"] [/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: #D9D9D9"]16[/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: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: #D9D9D9"]17[/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"]18[/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: 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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

Jyggalag
  • Question Question
Help me with copy paste macro please
Replies
8
Views
202
Jyggalag
Jyggalag
D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
1K
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
759
mumps
mumps
E
  • Question Question
Adding a custom message.
Replies
1
Views
734
Micron
M
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
3K
Attrazion
A

Share this page

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
Back
Top