Challenging Macro help

Jemma Atkinson · Mar 23, 2011

Hi, i have a work task which is very manual, i was hoping a macro could automate this. I have a list of brokers which buys and sells FX, i need a macro to create a tab for each currency and put the Buy and sells in one column for each broker, i am after a layout as shown below for the USD.

Please take note of Rawdata columns, i have hidden the columns which are not in use. Below is just an example, just to show you what i want the macro to do.

Excel Workbook

E

F

I

J

T

1

BUY_CURRENCY

BUY_QUANTITY

SELL_CURRENCY

SELL_QUANTITY

EXEC_BROKER

2

AUD

1478500

USD

1500677.5

DEUTSCHE BANK AG CORP.

3

USD

668304.76

KRW

744491500

BARCLAYS BANK PLC WHOLESA

4

TWD

13753200

USD

479372.6

STATE STREET BANK AND TRU

5

KRW

654212700

USD

589593.28

BARCLAYS BANK PLC WHOLESA

6

USD

446627.37

HUF

88214600

UBS SECURITIES L.L.C.

7

CZK

7746200

USD

443859.98

BARCLAYS BANK PLC WHOLESA

8

USD

439083.93

CLP

212143400

THE ROYAL BANK OF SCOTLAN

9

BRL

734200

USD

438328.36

BARCLAYS BANK PLC WHOLESA

10

MXN

5336600

USD

443158.23

BARCLAYS BANK PLC WHOLESA

11

CZK

7759200

USD

444632.91

UBS SECURITIES L.L.C.

12

EUR

1066800

USD

1470669.14

CREDIT SUISSE

13

USD

1528258.82

AUD

1514300

STATE STREET BANK AND TRU

14

EUR

4078200

USD

5622320.71

STATE STREET BANK AND TRU

15

CAD

4122200

USD

4153220.4

STATE STREET BANK AND TRU

16

AUD

719100

USD

725697.02

CITIBANK, N.A.

17

EUR

3117700

USD

4298694.11

CITIBANK, N.A.

18

CAD

2792300

USD

2813910.84

CITIBANK, N.A.

19

AUD

1206800

USD

1217781.88

BARCLAYS BANK PLC WHOLESA

Rawdata

Excel Workbook

A

B

C

D

E

F

G

H

I

1

Broker

CCY

Quantity

Method

Broker

CCY

Quantity

Method

2

BANC OF AMERICA SECURITIE

USD

23,829.88

BUY

BARCLAYS BANK PLC WHOLESA

USD

668,304.76

BUY

3

BANC OF AMERICA SECURITIE

USD

116,330.39

BUY

BARCLAYS BANK PLC WHOLESA

USD

4,071,417.78

BUY

4

BANC OF AMERICA SECURITIE

USD

6,651,688.08

BUY

BARCLAYS BANK PLC WHOLESA

USD

201,583.80

BUY

5

BANC OF AMERICA SECURITIE

USD

1,477,594.34

BUY

BARCLAYS BANK PLC WHOLESA

USD

812,395.09

BUY

6

BANC OF AMERICA SECURITIE

USD

179,715.67

BUY

BARCLAYS BANK PLC WHOLESA

USD

818,319.84

BUY

7

BANC OF AMERICA SECURITIE

USD

206,333.42

BUY

BARCLAYS BANK PLC WHOLESA

USD

1,614,417.63

BUY

8

BANC OF AMERICA SECURITIE

USD

1,459,231.67

BUY

BARCLAYS BANK PLC WHOLESA

USD

491,060.89

BUY

9

BANC OF AMERICA SECURITIE

USD

862,380.85

BUY

BARCLAYS BANK PLC WHOLESA

USD

436,926.61

BUY

10

BANC OF AMERICA SECURITIE

USD

436,319.31

BUY

BARCLAYS BANK PLC WHOLESA

USD

435,050.89

BUY

11

BANC OF AMERICA SECURITIE

USD

440,696.11

BUY

BARCLAYS BANK PLC WHOLESA

USD

434,180.39

BUY

12

BANC OF AMERICA SECURITIE

USD

1,502,044.36

BUY

BARCLAYS BANK PLC WHOLESA

USD

434,059.85

BUY

13

BANC OF AMERICA SECURITIE

USD

1,822,838.50

BUY

BARCLAYS BANK PLC WHOLESA

USD

1,459,465.03

BUY

14

BANC OF AMERICA SECURITIE

USD

334,199.37

SELL

BARCLAYS BANK PLC WHOLESA

USD

589593.28

SELL

15

BANC OF AMERICA SECURITIE

USD

441,367.24

SELL

BARCLAYS BANK PLC WHOLESA

USD

443859.98

SELL

16

BANC OF AMERICA SECURITIE

USD

435,944.41

SELL

BARCLAYS BANK PLC WHOLESA

USD

438328.36

SELL

17

BANC OF AMERICA SECURITIE

USD

1,446,390.60

SELL

BARCLAYS BANK PLC WHOLESA

USD

443158.23

SELL

18

BANC OF AMERICA SECURITIE

USD

442,413.33

SELL

BARCLAYS BANK PLC WHOLESA

USD

1217781.88

SELL

19

BANC OF AMERICA SECURITIE

USD

733,387.80

SELL

BARCLAYS BANK PLC WHOLESA

USD

289598.4

SELL

20

BANC OF AMERICA SECURITIE

USD

444,025.92

SELL

BARCLAYS BANK PLC WHOLESA

USD

11798943.9

SELL

21

BANC OF AMERICA SECURITIE

USD

235,913.98

SELL

BARCLAYS BANK PLC WHOLESA

USD

1341943.93

SELL

22

BANC OF AMERICA SECURITIE

USD

986,223.99

SELL

BARCLAYS BANK PLC WHOLESA

USD

46593.17

SELL

23

BANC OF AMERICA SECURITIE

USD

123,505.75

SELL

BARCLAYS BANK PLC WHOLESA

USD

460306.13

SELL

24

BANC OF AMERICA SECURITIE

USD

6,746,424.24

SELL

BARCLAYS BANK PLC WHOLESA

USD

687199.77

SELL

25

BANC OF AMERICA SECURITIE

USD

746,351.08

SELL

BARCLAYS BANK PLC WHOLESA

USD

28067.7

SELL

USD

bertie · Mar 23, 2011

Hi Jemma,

My attempt at a solution has four processes.

Code:

[COLOR=darkblue]Public[/COLOR] [COLOR=darkblue]Sub[/COLOR] Jemma()
   AddTempSheet
   ReOrganizeData
   ProcessReOrganizedData
   [COLOR=green]'DeleteTempSheet[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

First a temporary sheet is added.

Then ReOrganizeData pulls the relevant data into the temp sheet and sorts it on Currency, Broker and Method. I have commented out the DeleteTempSheet procedure to let you see this output.

Then ProcessReOrganisedData creates the worksheets and grops like brokers.

To test.
Copy your sample data into Sheet1 of a new workbook.
Press Alt+F11 to open the VBA Editor.
Double click on the ThisWorkbook Module.
Copy and paste the code.

You will get a better understanding of what the code does if you step through - F8 - the Jemma procedure.

Code:

[COLOR=darkblue]Public[/COLOR] [COLOR=darkblue]Sub[/COLOR] Jemma()
   AddTempSheet
   ReOrganizeData
   ProcessReOrganizedData
   [COLOR=green]'DeleteTempSheet[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] AddTempSheet()
 
   [COLOR=green]'delete any existing temp sheet[/COLOR]
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
      Application.DisplayAlerts = [COLOR=darkblue]False[/COLOR]
         Sheets("Temp").Delete
      Application.DisplayAlerts = [COLOR=darkblue]True[/COLOR]
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
   [COLOR=green]'add a temp sheet with headers[/COLOR]
   Worksheets.Add After:=Sheets(Sheets.Count)
   ActiveSheet.Name = "Temp"
   [COLOR=darkblue]With[/COLOR] Sheets("Temp")
      .Range("A1").Value = "Broker"
      .Range("B1").Value = "CCY"
      .Range("C1").Value = "Quantity"
      .Range("D1").Value = "Method"
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
   [COLOR=green]'colour yellow[/COLOR]
   Sheets("Temp").Range("A1:D1").Interior.ColorIndex = 6
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] ReOrganizeData()
   [COLOR=darkblue]Dim[/COLOR] rng [COLOR=darkblue]As[/COLOR] Range
   [COLOR=darkblue]Dim[/COLOR] rw [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   [COLOR=darkblue]Set[/COLOR] rng = Sheets("[COLOR=red]Sheet1[/COLOR]").Range("T2")
 
   [COLOR=green]'reorganize the data[/COLOR]
 
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rng = ""
      rw = rw + 2
      [COLOR=darkblue]With[/COLOR] Sheets("Temp")
         [COLOR=green]'buy[/COLOR]
         .Range("A" & rw).Value = rng.Value                   'col T Broker
         .Range("B" & rw).Value = rng.Offset(, -15).Value     [COLOR=green]'col E buy Curr[/COLOR]
         .Range("C" & rw).Value = rng.Offset(, -14).Value     [COLOR=green]'col F buy qty[/COLOR]
         .Range("D" & rw).Value = "BUY"                       'buy
 
         [COLOR=green]'sell[/COLOR]
         .Range("A" & rw + 1).Value = rng.Value               'col T Broker
         .Range("B" & rw + 1).Value = rng.Offset(, -11).Value [COLOR=green]'col I sell Curr[/COLOR]
         .Range("C" & rw + 1).Value = rng.Offset(, -10).Value [COLOR=green]'col J sell qty[/COLOR]
         .Range("D" & rw + 1).Value = "SELL"
 
         [COLOR=darkblue]Set[/COLOR] rng = rng.Offset(1, 0)
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
   [COLOR=darkblue]Loop[/COLOR]
 
   [COLOR=green]'sort the re-organized data[/COLOR]
   [COLOR=darkblue]With[/COLOR] Sheets("Temp")
         .Range("A1:D" & rw + 1).Sort _
         Key1:=Range("B2"), Order1:=xlAscending, _
         Key2:=Range("A2"), Order2:=xlAscending, _
         Key3:=Range("D2"), Order2:=xlAscending, _
         Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
         Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
         DataOption2:=xlSortNormal
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
   Sheets("Temp").Columns("A:D").EntireColumn.AutoFit
   [COLOR=darkblue]Set[/COLOR] rng = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] ProcessReOrganizedData()
   [COLOR=darkblue]Dim[/COLOR] wsNew [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] wsTemp [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] rngTemp [COLOR=darkblue]As[/COLOR] Range
   [COLOR=darkblue]Dim[/COLOR] sCurr [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]           [COLOR=green]'currency[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] sBroker [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]         'broker
   [COLOR=darkblue]Dim[/COLOR] rowNew [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] colNew [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
 
   [COLOR=darkblue]Set[/COLOR] wsTemp = Sheets("Temp")
   [COLOR=darkblue]Set[/COLOR] rngTemp = wsTemp.Range("B2")
   sCurr = ""
   sBroker = ""
   rowNew = 1
 
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rngTemp = ""
      [COLOR=green]'do we need to cretae a new worksheet?[/COLOR]
      [COLOR=darkblue]If[/COLOR] sCurr <> rngTemp.Value [COLOR=darkblue]Then[/COLOR]
         sCurr = rngTemp.Value
         Worksheets.Add After:=Sheets("Temp")
         ActiveSheet.Name = sCurr
         [COLOR=darkblue]Set[/COLOR] wsNew = Sheets(sCurr)
         rowNew = 2
         colNew = 1
 
         [COLOR=green]'copy headers[/COLOR]
         wsTemp.Range("A1:D1").Copy Destination:=wsNew.Cells(1, colNew)
         [COLOR=green]'copy values[/COLOR]
         wsTemp.Range("A" & rngTemp.Row & ":D" & rngTemp.Row).Copy _
            Destination:=wsNew.Cells(rowNew, colNew)
 
         [COLOR=green]'reset the broker[/COLOR]
         sBroker = rngTemp.Offset(0, -1).Value
 
      [COLOR=darkblue]Else[/COLOR]
         [COLOR=green]'check the broker[/COLOR]
         [COLOR=darkblue]If[/COLOR] sBroker = rngTemp.Offset(0, -1).Value [COLOR=darkblue]Then[/COLOR]
            rowNew = rowNew + 1
            [COLOR=green]'copy values[/COLOR]
            wsTemp.Range("A" & rngTemp.Row & ":D" & rngTemp.Row).Copy _
            Destination:=wsNew.Cells(rowNew, colNew)
         [COLOR=darkblue]Else[/COLOR]
            [COLOR=green]'move output along five columns[/COLOR]
            rowNew = 2
            colNew = colNew + 5
 
            [COLOR=green]'copy headers[/COLOR]
            wsTemp.Range("A1:D1").Copy Destination:=wsNew.Cells(1, colNew)
            [COLOR=green]'copy values[/COLOR]
            wsTemp.Range("A" & rngTemp.Row & ":D" & rngTemp.Row).Copy _
               Destination:=wsNew.Cells(rowNew, colNew)
 
            [COLOR=green]'reset the broker[/COLOR]
            sBroker = rngTemp.Offset(0, -1).Value
 
          [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
 
      [COLOR=darkblue]Set[/COLOR] rngTemp = rngTemp.Offset(1, 0)
      [COLOR=green]'sCurr = rngTemp.Value[/COLOR]
   [COLOR=darkblue]Loop[/COLOR]
 
   [COLOR=darkblue]Set[/COLOR] rngTemp = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wsNew = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wsTemp = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] DeleteTempSheet()
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
 
   Application.DisplayAlerts = [COLOR=darkblue]False[/COLOR]
      Sheets("Temp").Delete
   Application.DisplayAlerts = [COLOR=darkblue]True[/COLOR]
 
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Jemma Atkinson · Mar 23, 2011

Bertie, this is awesome, great work, this will save me 30mins, it was painful doing it manually.

Is it possible to add numberformating .NumberFormat = "#,##0.00_);[Red](#,##0.00)" to all the numbers and do Entirecolumn.Autofit for all currency tabs

bertie said:

Hi Jemma,

My attempt at a solution has four processes.

Code:

[COLOR=darkblue]Public[/COLOR] [COLOR=darkblue]Sub[/COLOR] Jemma()
   AddTempSheet
   ReOrganizeData
   ProcessReOrganizedData
   [COLOR=green]'DeleteTempSheet[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

First a temporary sheet is added.

Then ReOrganizeData pulls the relevant data into the temp sheet and sorts it on Currency, Broker and Method. I have commented out the DeleteTempSheet procedure to let you see this output.

Then ProcessReOrganisedData creates the worksheets and grops like brokers.

To test.
Copy your sample data into Sheet1 of a new workbook.
Press Alt+F11 to open the VBA Editor.
Double click on the ThisWorkbook Module.
Copy and paste the code.

You will get a better understanding of what the code does if you step through - F8 - the Jemma procedure.

Code:

[COLOR=darkblue]Public[/COLOR] [COLOR=darkblue]Sub[/COLOR] Jemma()
   AddTempSheet
   ReOrganizeData
   ProcessReOrganizedData
   [COLOR=green]'DeleteTempSheet[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] AddTempSheet()
 
   [COLOR=green]'delete any existing temp sheet[/COLOR]
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
      Application.DisplayAlerts = [COLOR=darkblue]False[/COLOR]
         Sheets("Temp").Delete
      Application.DisplayAlerts = [COLOR=darkblue]True[/COLOR]
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
   [COLOR=green]'add a temp sheet with headers[/COLOR]
   Worksheets.Add After:=Sheets(Sheets.Count)
   ActiveSheet.Name = "Temp"
   [COLOR=darkblue]With[/COLOR] Sheets("Temp")
      .Range("A1").Value = "Broker"
      .Range("B1").Value = "CCY"
      .Range("C1").Value = "Quantity"
      .Range("D1").Value = "Method"
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
   [COLOR=green]'colour yellow[/COLOR]
   Sheets("Temp").Range("A1:D1").Interior.ColorIndex = 6
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] ReOrganizeData()
   [COLOR=darkblue]Dim[/COLOR] rng [COLOR=darkblue]As[/COLOR] Range
   [COLOR=darkblue]Dim[/COLOR] rw [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   [COLOR=darkblue]Set[/COLOR] rng = Sheets("[COLOR=red]Sheet1[/COLOR]").Range("T2")
 
   [COLOR=green]'reorganize the data[/COLOR]
 
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rng = ""
      rw = rw + 2
      [COLOR=darkblue]With[/COLOR] Sheets("Temp")
         [COLOR=green]'buy[/COLOR]
         .Range("A" & rw).Value = rng.Value                   'col T Broker
         .Range("B" & rw).Value = rng.Offset(, -15).Value     [COLOR=green]'col E buy Curr[/COLOR]
         .Range("C" & rw).Value = rng.Offset(, -14).Value     [COLOR=green]'col F buy qty[/COLOR]
         .Range("D" & rw).Value = "BUY"                       'buy
 
         [COLOR=green]'sell[/COLOR]
         .Range("A" & rw + 1).Value = rng.Value               'col T Broker
         .Range("B" & rw + 1).Value = rng.Offset(, -11).Value [COLOR=green]'col I sell Curr[/COLOR]
         .Range("C" & rw + 1).Value = rng.Offset(, -10).Value [COLOR=green]'col J sell qty[/COLOR]
         .Range("D" & rw + 1).Value = "SELL"
 
         [COLOR=darkblue]Set[/COLOR] rng = rng.Offset(1, 0)
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
   [COLOR=darkblue]Loop[/COLOR]
 
   [COLOR=green]'sort the re-organized data[/COLOR]
   [COLOR=darkblue]With[/COLOR] Sheets("Temp")
         .Range("A1:D" & rw + 1).Sort _
         Key1:=Range("B2"), Order1:=xlAscending, _
         Key2:=Range("A2"), Order2:=xlAscending, _
         Key3:=Range("D2"), Order2:=xlAscending, _
         Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
         Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
         DataOption2:=xlSortNormal
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
   Sheets("Temp").Columns("A:D").EntireColumn.AutoFit
   [COLOR=darkblue]Set[/COLOR] rng = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] ProcessReOrganizedData()
   [COLOR=darkblue]Dim[/COLOR] wsNew [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] wsTemp [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] rngTemp [COLOR=darkblue]As[/COLOR] Range
   [COLOR=darkblue]Dim[/COLOR] sCurr [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]           [COLOR=green]'currency[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] sBroker [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]         'broker
   [COLOR=darkblue]Dim[/COLOR] rowNew [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] colNew [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
 
   [COLOR=darkblue]Set[/COLOR] wsTemp = Sheets("Temp")
   [COLOR=darkblue]Set[/COLOR] rngTemp = wsTemp.Range("B2")
   sCurr = ""
   sBroker = ""
   rowNew = 1
 
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rngTemp = ""
      [COLOR=green]'do we need to cretae a new worksheet?[/COLOR]
      [COLOR=darkblue]If[/COLOR] sCurr <> rngTemp.Value [COLOR=darkblue]Then[/COLOR]
         sCurr = rngTemp.Value
         Worksheets.Add After:=Sheets("Temp")
         ActiveSheet.Name = sCurr
         [COLOR=darkblue]Set[/COLOR] wsNew = Sheets(sCurr)
         rowNew = 2
         colNew = 1
 
         [COLOR=green]'copy headers[/COLOR]
         wsTemp.Range("A1:D1").Copy Destination:=wsNew.Cells(1, colNew)
         [COLOR=green]'copy values[/COLOR]
         wsTemp.Range("A" & rngTemp.Row & ":D" & rngTemp.Row).Copy _
            Destination:=wsNew.Cells(rowNew, colNew)
 
         [COLOR=green]'reset the broker[/COLOR]
         sBroker = rngTemp.Offset(0, -1).Value
 
      [COLOR=darkblue]Else[/COLOR]
         [COLOR=green]'check the broker[/COLOR]
         [COLOR=darkblue]If[/COLOR] sBroker = rngTemp.Offset(0, -1).Value [COLOR=darkblue]Then[/COLOR]
            rowNew = rowNew + 1
            [COLOR=green]'copy values[/COLOR]
            wsTemp.Range("A" & rngTemp.Row & ":D" & rngTemp.Row).Copy _
            Destination:=wsNew.Cells(rowNew, colNew)
         [COLOR=darkblue]Else[/COLOR]
            [COLOR=green]'move output along five columns[/COLOR]
            rowNew = 2
            colNew = colNew + 5
 
            [COLOR=green]'copy headers[/COLOR]
            wsTemp.Range("A1:D1").Copy Destination:=wsNew.Cells(1, colNew)
            [COLOR=green]'copy values[/COLOR]
            wsTemp.Range("A" & rngTemp.Row & ":D" & rngTemp.Row).Copy _
               Destination:=wsNew.Cells(rowNew, colNew)
 
            [COLOR=green]'reset the broker[/COLOR]
            sBroker = rngTemp.Offset(0, -1).Value
 
          [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
 
      [COLOR=darkblue]Set[/COLOR] rngTemp = rngTemp.Offset(1, 0)
      [COLOR=green]'sCurr = rngTemp.Value[/COLOR]
   [COLOR=darkblue]Loop[/COLOR]
 
   [COLOR=darkblue]Set[/COLOR] rngTemp = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wsNew = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wsTemp = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] DeleteTempSheet()
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
 
   Application.DisplayAlerts = [COLOR=darkblue]False[/COLOR]
      Sheets("Temp").Delete
   Application.DisplayAlerts = [COLOR=darkblue]True[/COLOR]
 
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

bertie · Mar 24, 2011

Hi Jemma,

Can you clarify the number formatting for me.

We can apply the number formatting at the end of the ReOrganizeData procedure:

Code:

   [COLOR=darkblue]With[/COLOR] Sheets("Temp").Range("C2:C" & rw)
      .NumberFormat = "#,##0.00_ ;[Red]#,##0.00"
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
   Sheets("Temp").Columns("A:D").EntireColumn.AutoFit
   [COLOR=darkblue]Set[/COLOR] rng = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

But all the numbers in the sample data are possitive so the [Red] part of the formatting has no effect.

On looking at your sample data I notice that some commodities are sold for less than the buying price. Is this what you are trying to highlight?

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 87px"><COL style="WIDTH: 15px"><COL style="WIDTH: 13px"><COL style="WIDTH: 74px"><COL style="WIDTH: 87px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 35px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">BUY_QUANTITY</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana">g</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana">h</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">SELL_CURRENCY</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">SELL_QUANTITY</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">1206800</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana">USD</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">1217781.9</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">719100</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana">USD</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">725697.02</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">1478500</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana">USD</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">1500677.5</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">734200</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana">USD</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; COLOR: #ff0000">438328.36</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">2792300</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana">USD</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">2813910.8</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">4122200</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana">USD</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">4153220.4</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">7746200</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana">USD</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; COLOR: #ff0000">443859.98</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">7759200</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana">USD</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; COLOR: #ff0000">444632.91</TD></TR></TBODY></TABLE>

bertie · Mar 24, 2011

Hi Jemma,

Ignore my last post. I notice we are dealing with different currencies. No wonder I'm skint all the time.

Try this. I have highlighted the changes I have made.

Code:

[COLOR=darkblue]Public[/COLOR] [COLOR=darkblue]Sub[/COLOR] Jemma()
   AddTempSheet
   ReOrganizeData
   ProcessReOrganizedData
   [COLOR=green]'DeleteTempSheet[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] AddTempSheet()
 
   [COLOR=green]'delete any existing temp sheet[/COLOR]
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
      Application.DisplayAlerts = [COLOR=darkblue]False[/COLOR]
         Sheets("Temp").Delete
      Application.DisplayAlerts = [COLOR=darkblue]True[/COLOR]
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
   [COLOR=green]'add a temp sheet with headers[/COLOR]
   Worksheets.Add After:=Sheets(Sheets.Count)
   ActiveSheet.Name = "Temp"
   [COLOR=darkblue]With[/COLOR] Sheets("Temp")
      .Range("A1").Value = "Broker"
      .Range("B1").Value = "CCY"
      .Range("C1").Value = "Quantity"
      .Range("D1").Value = "Method"
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
   [COLOR=green]'colour yellow[/COLOR]
   Sheets("Temp").Range("A1:D1").Interior.ColorIndex = 6
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] ReOrganizeData()
   [COLOR=darkblue]Dim[/COLOR] rng [COLOR=darkblue]As[/COLOR] Range
   [COLOR=darkblue]Dim[/COLOR] rw [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   [COLOR=darkblue]Set[/COLOR] rng = Sheets("Sheet1").Range("T2")
 
   [COLOR=green]'reorganize the data[/COLOR]
 
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rng = ""
      rw = rw + 2
      [COLOR=darkblue]With[/COLOR] Sheets("Temp")
         [COLOR=green]'buy[/COLOR]
         .Range("A" & rw).Value = rng.Value                   'col T Broker
         .Range("B" & rw).Value = rng.Offset(, -15).Value     [COLOR=green]'col E buy Curr[/COLOR]
         .Range("C" & rw).Value = rng.Offset(, -14).Value     [COLOR=green]'col F buy qty[/COLOR]
         .Range("D" & rw).Value = "BUY"                       'buy
 
         [COLOR=green]'sell[/COLOR]
         .Range("A" & rw + 1).Value = rng.Value               'col T Broker
         .Range("B" & rw + 1).Value = rng.Offset(, -11).Value [COLOR=green]'col I sell Curr[/COLOR]
         .Range("C" & rw + 1).Value = rng.Offset(, -10).Value [COLOR=green]'col J sell qty[/COLOR]
         .Range("D" & rw + 1).Value = "SELL"
 
         [COLOR=darkblue]Set[/COLOR] rng = rng.Offset(1, 0)
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
   [COLOR=darkblue]Loop[/COLOR]
 
   [COLOR=green]'sort the re-organized data[/COLOR]
   [COLOR=darkblue]With[/COLOR] Sheets("Temp")
         .Range("A1:D" & rw + 1).Sort _
         Key1:=Range("B2"), Order1:=xlAscending, _
         Key2:=Range("A2"), Order2:=xlAscending, _
         Key3:=Range("D2"), Order2:=xlAscending, _
         Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
         Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
         DataOption2:=xlSortNormal
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
[COLOR=red] With Sheets("Temp").Range("C2:C" & rw)[/COLOR]
[COLOR=red]    .NumberFormat = "#,##0.00_ ;[Red]#,##0.00"[/COLOR]
[COLOR=red] End With[/COLOR]
 
   Sheets("Temp").Columns("A:D").EntireColumn.AutoFit
   [COLOR=darkblue]Set[/COLOR] rng = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] ProcessReOrganizedData()
   [COLOR=darkblue]Dim[/COLOR] wsNew [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] wsTemp [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] rngTemp [COLOR=darkblue]As[/COLOR] Range
   [COLOR=darkblue]Dim[/COLOR] sCurr [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]           [COLOR=green]'currency[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] sBroker [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]         'broker
   [COLOR=darkblue]Dim[/COLOR] rowNew [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] colNew [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
 
   [COLOR=darkblue]Set[/COLOR] wsTemp = Sheets("Temp")
   [COLOR=darkblue]Set[/COLOR] rngTemp = wsTemp.Range("B2")
   sCurr = ""
   sBroker = ""
   rowNew = 1
 
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rngTemp = ""
      [COLOR=green]'do we need to cretae a new worksheet?[/COLOR]
      [COLOR=darkblue]If[/COLOR] sCurr <> rngTemp.Value [COLOR=darkblue]Then[/COLOR]
         sCurr = rngTemp.Value
         Worksheets.Add After:=Sheets("Temp")
         ActiveSheet.Name = sCurr
         [COLOR=darkblue]Set[/COLOR] wsNew = Sheets(sCurr)
         rowNew = 2
         colNew = 1
 
         [COLOR=green]'copy headers[/COLOR]
         wsTemp.Range("A1:D1").Copy Destination:=wsNew.Cells(1, colNew)
         [COLOR=green]'copy values[/COLOR]
         wsTemp.Range("A" & rngTemp.Row & ":D" & rngTemp.Row).Copy _
            Destination:=wsNew.Cells(rowNew, colNew)
 
         [COLOR=green]'reset the broker[/COLOR]
         sBroker = rngTemp.Offset(0, -1).Value
 
      [COLOR=darkblue]Else[/COLOR]
         [COLOR=green]'check the broker[/COLOR]
         [COLOR=darkblue]If[/COLOR] sBroker = rngTemp.Offset(0, -1).Value [COLOR=darkblue]Then[/COLOR]
            rowNew = rowNew + 1
            [COLOR=green]'copy values[/COLOR]
            wsTemp.Range("A" & rngTemp.Row & ":D" & rngTemp.Row).Copy _
            Destination:=wsNew.Cells(rowNew, colNew)
         [COLOR=darkblue]Else[/COLOR]
            [COLOR=green]'move output along five columns[/COLOR]
            rowNew = 2
            colNew = colNew + 5
 
            [COLOR=green]'copy headers[/COLOR]
            wsTemp.Range("A1:D1").Copy Destination:=wsNew.Cells(1, colNew)
            [COLOR=green]'copy values[/COLOR]
            wsTemp.Range("A" & rngTemp.Row & ":D" & rngTemp.Row).Copy _
               Destination:=wsNew.Cells(rowNew, colNew)
 
            [COLOR=green]'reset the broker[/COLOR]
            sBroker = rngTemp.Offset(0, -1).Value
 
          [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
 
      [COLOR=darkblue]Set[/COLOR] rngTemp = rngTemp.Offset(1, 0)
      [COLOR=green]'sCurr = rngTemp.Value[/COLOR]
 
      [COLOR=green]'autofit the columns[/COLOR]
      [COLOR=red]wsNew.Cells.EntireColumn.AutoFit[/COLOR]
   [COLOR=darkblue]Loop[/COLOR]
 
   [COLOR=darkblue]Set[/COLOR] rngTemp = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wsNew = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wsTemp = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] DeleteTempSheet()
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
 
   Application.DisplayAlerts = [COLOR=darkblue]False[/COLOR]
      Sheets("Temp").Delete
   Application.DisplayAlerts = [COLOR=darkblue]True[/COLOR]
 
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Jemma Atkinson · Mar 24, 2011

Top man Bertie, this is great! You have saved me so much time, thanks mate

bertie said:

Hi Jemma,

Ignore my last post. I notice we are dealing with different currencies. No wonder I'm skint all the time.

Try this. I have highlighted the changes I have made.

Code:

[COLOR=darkblue]Public[/COLOR] [COLOR=darkblue]Sub[/COLOR] Jemma()
   AddTempSheet
   ReOrganizeData
   ProcessReOrganizedData
   [COLOR=green]'DeleteTempSheet[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] AddTempSheet()
 
   [COLOR=green]'delete any existing temp sheet[/COLOR]
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
      Application.DisplayAlerts = [COLOR=darkblue]False[/COLOR]
         Sheets("Temp").Delete
      Application.DisplayAlerts = [COLOR=darkblue]True[/COLOR]
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
   [COLOR=green]'add a temp sheet with headers[/COLOR]
   Worksheets.Add After:=Sheets(Sheets.Count)
   ActiveSheet.Name = "Temp"
   [COLOR=darkblue]With[/COLOR] Sheets("Temp")
      .Range("A1").Value = "Broker"
      .Range("B1").Value = "CCY"
      .Range("C1").Value = "Quantity"
      .Range("D1").Value = "Method"
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
   [COLOR=green]'colour yellow[/COLOR]
   Sheets("Temp").Range("A1:D1").Interior.ColorIndex = 6
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] ReOrganizeData()
   [COLOR=darkblue]Dim[/COLOR] rng [COLOR=darkblue]As[/COLOR] Range
   [COLOR=darkblue]Dim[/COLOR] rw [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   [COLOR=darkblue]Set[/COLOR] rng = Sheets("Sheet1").Range("T2")
 
   [COLOR=green]'reorganize the data[/COLOR]
 
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rng = ""
      rw = rw + 2
      [COLOR=darkblue]With[/COLOR] Sheets("Temp")
         [COLOR=green]'buy[/COLOR]
         .Range("A" & rw).Value = rng.Value                   'col T Broker
         .Range("B" & rw).Value = rng.Offset(, -15).Value     [COLOR=green]'col E buy Curr[/COLOR]
         .Range("C" & rw).Value = rng.Offset(, -14).Value     [COLOR=green]'col F buy qty[/COLOR]
         .Range("D" & rw).Value = "BUY"                       'buy
 
         [COLOR=green]'sell[/COLOR]
         .Range("A" & rw + 1).Value = rng.Value               'col T Broker
         .Range("B" & rw + 1).Value = rng.Offset(, -11).Value [COLOR=green]'col I sell Curr[/COLOR]
         .Range("C" & rw + 1).Value = rng.Offset(, -10).Value [COLOR=green]'col J sell qty[/COLOR]
         .Range("D" & rw + 1).Value = "SELL"
 
         [COLOR=darkblue]Set[/COLOR] rng = rng.Offset(1, 0)
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
   [COLOR=darkblue]Loop[/COLOR]
 
   [COLOR=green]'sort the re-organized data[/COLOR]
   [COLOR=darkblue]With[/COLOR] Sheets("Temp")
         .Range("A1:D" & rw + 1).Sort _
         Key1:=Range("B2"), Order1:=xlAscending, _
         Key2:=Range("A2"), Order2:=xlAscending, _
         Key3:=Range("D2"), Order2:=xlAscending, _
         Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
         Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
         DataOption2:=xlSortNormal
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
[COLOR=red] With Sheets("Temp").Range("C2:C" & rw)[/COLOR]
[COLOR=red]    .NumberFormat = "#,##0.00_ ;[Red]#,##0.00"[/COLOR]
[COLOR=red] End With[/COLOR]
 
   Sheets("Temp").Columns("A:D").EntireColumn.AutoFit
   [COLOR=darkblue]Set[/COLOR] rng = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] ProcessReOrganizedData()
   [COLOR=darkblue]Dim[/COLOR] wsNew [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] wsTemp [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] rngTemp [COLOR=darkblue]As[/COLOR] Range
   [COLOR=darkblue]Dim[/COLOR] sCurr [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]           [COLOR=green]'currency[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] sBroker [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]         'broker
   [COLOR=darkblue]Dim[/COLOR] rowNew [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] colNew [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
 
   [COLOR=darkblue]Set[/COLOR] wsTemp = Sheets("Temp")
   [COLOR=darkblue]Set[/COLOR] rngTemp = wsTemp.Range("B2")
   sCurr = ""
   sBroker = ""
   rowNew = 1
 
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rngTemp = ""
      [COLOR=green]'do we need to cretae a new worksheet?[/COLOR]
      [COLOR=darkblue]If[/COLOR] sCurr <> rngTemp.Value [COLOR=darkblue]Then[/COLOR]
         sCurr = rngTemp.Value
         Worksheets.Add After:=Sheets("Temp")
         ActiveSheet.Name = sCurr
         [COLOR=darkblue]Set[/COLOR] wsNew = Sheets(sCurr)
         rowNew = 2
         colNew = 1
 
         [COLOR=green]'copy headers[/COLOR]
         wsTemp.Range("A1:D1").Copy Destination:=wsNew.Cells(1, colNew)
         [COLOR=green]'copy values[/COLOR]
         wsTemp.Range("A" & rngTemp.Row & ":D" & rngTemp.Row).Copy _
            Destination:=wsNew.Cells(rowNew, colNew)
 
         [COLOR=green]'reset the broker[/COLOR]
         sBroker = rngTemp.Offset(0, -1).Value
 
      [COLOR=darkblue]Else[/COLOR]
         [COLOR=green]'check the broker[/COLOR]
         [COLOR=darkblue]If[/COLOR] sBroker = rngTemp.Offset(0, -1).Value [COLOR=darkblue]Then[/COLOR]
            rowNew = rowNew + 1
            [COLOR=green]'copy values[/COLOR]
            wsTemp.Range("A" & rngTemp.Row & ":D" & rngTemp.Row).Copy _
            Destination:=wsNew.Cells(rowNew, colNew)
         [COLOR=darkblue]Else[/COLOR]
            [COLOR=green]'move output along five columns[/COLOR]
            rowNew = 2
            colNew = colNew + 5
 
            [COLOR=green]'copy headers[/COLOR]
            wsTemp.Range("A1:D1").Copy Destination:=wsNew.Cells(1, colNew)
            [COLOR=green]'copy values[/COLOR]
            wsTemp.Range("A" & rngTemp.Row & ":D" & rngTemp.Row).Copy _
               Destination:=wsNew.Cells(rowNew, colNew)
 
            [COLOR=green]'reset the broker[/COLOR]
            sBroker = rngTemp.Offset(0, -1).Value
 
          [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
 
      [COLOR=darkblue]Set[/COLOR] rngTemp = rngTemp.Offset(1, 0)
      [COLOR=green]'sCurr = rngTemp.Value[/COLOR]
 
      [COLOR=green]'autofit the columns[/COLOR]
      [COLOR=red]wsNew.Cells.EntireColumn.AutoFit[/COLOR]
   [COLOR=darkblue]Loop[/COLOR]
 
   [COLOR=darkblue]Set[/COLOR] rngTemp = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wsNew = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wsTemp = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] DeleteTempSheet()
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
 
   Application.DisplayAlerts = [COLOR=darkblue]False[/COLOR]
      Sheets("Temp").Delete
   Application.DisplayAlerts = [COLOR=darkblue]True[/COLOR]
 
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Jemma Atkinson · Apr 3, 2011

Hi Bertie, is it possible to add Total underneath each broker quantity, something like this

Excel Workbook

A

B

C

D

1

Broker

CCY

Quantity

Method

2

BANC OF AMERICA SECURITIE

USD

23,829.88

BUY

3

BANC OF AMERICA SECURITIE

USD

116,330.39

BUY

4

BANC OF AMERICA SECURITIE

USD

6,651,688.08

BUY

5

BANC OF AMERICA SECURITIE

USD

1,477,594.34

BUY

6

BANC OF AMERICA SECURITIE

USD

179,715.67

BUY

7

BANC OF AMERICA SECURITIE

USD

206,333.42

BUY

8

BANC OF AMERICA SECURITIE

USD

1,459,231.67

BUY

9

BANC OF AMERICA SECURITIE

USD

862,380.85

BUY

10

BANC OF AMERICA SECURITIE

USD

436,319.31

BUY

11

BANC OF AMERICA SECURITIE

USD

440,696.11

BUY

12

BANC OF AMERICA SECURITIE

USD

1,502,044.36

BUY

13

BANC OF AMERICA SECURITIE

USD

1,822,838.50

BUY

14

BANC OF AMERICA SECURITIE

USD

1,172,102.56

BUY

15

BANC OF AMERICA SECURITIE

USD

11,187,641.75

BUY

16

BANC OF AMERICA SECURITIE

USD

1,594,789.25

BUY

17

BANC OF AMERICA SECURITIE

USD

1,292,244.17

BUY

18

BANC OF AMERICA SECURITIE

USD

1,701,123.42

BUY

43

BANC OF AMERICA SECURITIE

USD

123,505.75

SELL

44

BANC OF AMERICA SECURITIE

USD

6,746,424.24

SELL

45

BANC OF AMERICA SECURITIE

USD

746,351.08

SELL

46

47

Total

66,856,484.37

48

USD

bertie said:

Hi Jemma,

Ignore my last post. I notice we are dealing with different currencies. No wonder I'm skint all the time.

Try this. I have highlighted the changes I have made.

Code:

[COLOR=darkblue]Public[/COLOR] [COLOR=darkblue]Sub[/COLOR] Jemma()
   AddTempSheet
   ReOrganizeData
   ProcessReOrganizedData
   [COLOR=green]'DeleteTempSheet[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] AddTempSheet()
 
   [COLOR=green]'delete any existing temp sheet[/COLOR]
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
      Application.DisplayAlerts = [COLOR=darkblue]False[/COLOR]
         Sheets("Temp").Delete
      Application.DisplayAlerts = [COLOR=darkblue]True[/COLOR]
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
   [COLOR=green]'add a temp sheet with headers[/COLOR]
   Worksheets.Add After:=Sheets(Sheets.Count)
   ActiveSheet.Name = "Temp"
   [COLOR=darkblue]With[/COLOR] Sheets("Temp")
      .Range("A1").Value = "Broker"
      .Range("B1").Value = "CCY"
      .Range("C1").Value = "Quantity"
      .Range("D1").Value = "Method"
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
   [COLOR=green]'colour yellow[/COLOR]
   Sheets("Temp").Range("A1:D1").Interior.ColorIndex = 6
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] ReOrganizeData()
   [COLOR=darkblue]Dim[/COLOR] rng [COLOR=darkblue]As[/COLOR] Range
   [COLOR=darkblue]Dim[/COLOR] rw [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   [COLOR=darkblue]Set[/COLOR] rng = Sheets("Sheet1").Range("T2")
 
   [COLOR=green]'reorganize the data[/COLOR]
 
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rng = ""
      rw = rw + 2
      [COLOR=darkblue]With[/COLOR] Sheets("Temp")
         [COLOR=green]'buy[/COLOR]
         .Range("A" & rw).Value = rng.Value                   'col T Broker
         .Range("B" & rw).Value = rng.Offset(, -15).Value     [COLOR=green]'col E buy Curr[/COLOR]
         .Range("C" & rw).Value = rng.Offset(, -14).Value     [COLOR=green]'col F buy qty[/COLOR]
         .Range("D" & rw).Value = "BUY"                       'buy
 
         [COLOR=green]'sell[/COLOR]
         .Range("A" & rw + 1).Value = rng.Value               'col T Broker
         .Range("B" & rw + 1).Value = rng.Offset(, -11).Value [COLOR=green]'col I sell Curr[/COLOR]
         .Range("C" & rw + 1).Value = rng.Offset(, -10).Value [COLOR=green]'col J sell qty[/COLOR]
         .Range("D" & rw + 1).Value = "SELL"
 
         [COLOR=darkblue]Set[/COLOR] rng = rng.Offset(1, 0)
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
   [COLOR=darkblue]Loop[/COLOR]
 
   [COLOR=green]'sort the re-organized data[/COLOR]
   [COLOR=darkblue]With[/COLOR] Sheets("Temp")
         .Range("A1:D" & rw + 1).Sort _
         Key1:=Range("B2"), Order1:=xlAscending, _
         Key2:=Range("A2"), Order2:=xlAscending, _
         Key3:=Range("D2"), Order2:=xlAscending, _
         Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
         Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
         DataOption2:=xlSortNormal
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
[COLOR=red] With Sheets("Temp").Range("C2:C" & rw)[/COLOR]
[COLOR=red]    .NumberFormat = "#,##0.00_ ;[Red]#,##0.00"[/COLOR]
[COLOR=red] End With[/COLOR]
 
   Sheets("Temp").Columns("A:D").EntireColumn.AutoFit
   [COLOR=darkblue]Set[/COLOR] rng = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] ProcessReOrganizedData()
   [COLOR=darkblue]Dim[/COLOR] wsNew [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] wsTemp [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] rngTemp [COLOR=darkblue]As[/COLOR] Range
   [COLOR=darkblue]Dim[/COLOR] sCurr [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]           [COLOR=green]'currency[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] sBroker [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]         'broker
   [COLOR=darkblue]Dim[/COLOR] rowNew [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] colNew [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
 
   [COLOR=darkblue]Set[/COLOR] wsTemp = Sheets("Temp")
   [COLOR=darkblue]Set[/COLOR] rngTemp = wsTemp.Range("B2")
   sCurr = ""
   sBroker = ""
   rowNew = 1
 
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rngTemp = ""
      [COLOR=green]'do we need to cretae a new worksheet?[/COLOR]
      [COLOR=darkblue]If[/COLOR] sCurr <> rngTemp.Value [COLOR=darkblue]Then[/COLOR]
         sCurr = rngTemp.Value
         Worksheets.Add After:=Sheets("Temp")
         ActiveSheet.Name = sCurr
         [COLOR=darkblue]Set[/COLOR] wsNew = Sheets(sCurr)
         rowNew = 2
         colNew = 1
 
         [COLOR=green]'copy headers[/COLOR]
         wsTemp.Range("A1:D1").Copy Destination:=wsNew.Cells(1, colNew)
         [COLOR=green]'copy values[/COLOR]
         wsTemp.Range("A" & rngTemp.Row & ":D" & rngTemp.Row).Copy _
            Destination:=wsNew.Cells(rowNew, colNew)
 
         [COLOR=green]'reset the broker[/COLOR]
         sBroker = rngTemp.Offset(0, -1).Value
 
      [COLOR=darkblue]Else[/COLOR]
         [COLOR=green]'check the broker[/COLOR]
         [COLOR=darkblue]If[/COLOR] sBroker = rngTemp.Offset(0, -1).Value [COLOR=darkblue]Then[/COLOR]
            rowNew = rowNew + 1
            [COLOR=green]'copy values[/COLOR]
            wsTemp.Range("A" & rngTemp.Row & ":D" & rngTemp.Row).Copy _
            Destination:=wsNew.Cells(rowNew, colNew)
         [COLOR=darkblue]Else[/COLOR]
            [COLOR=green]'move output along five columns[/COLOR]
            rowNew = 2
            colNew = colNew + 5
 
            [COLOR=green]'copy headers[/COLOR]
            wsTemp.Range("A1:D1").Copy Destination:=wsNew.Cells(1, colNew)
            [COLOR=green]'copy values[/COLOR]
            wsTemp.Range("A" & rngTemp.Row & ":D" & rngTemp.Row).Copy _
               Destination:=wsNew.Cells(rowNew, colNew)
 
            [COLOR=green]'reset the broker[/COLOR]
            sBroker = rngTemp.Offset(0, -1).Value
 
          [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
 
      [COLOR=darkblue]Set[/COLOR] rngTemp = rngTemp.Offset(1, 0)
      [COLOR=green]'sCurr = rngTemp.Value[/COLOR]
 
      [COLOR=green]'autofit the columns[/COLOR]
      [COLOR=red]wsNew.Cells.EntireColumn.AutoFit[/COLOR]
   [COLOR=darkblue]Loop[/COLOR]
 
   [COLOR=darkblue]Set[/COLOR] rngTemp = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wsNew = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wsTemp = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] DeleteTempSheet()
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
 
   Application.DisplayAlerts = [COLOR=darkblue]False[/COLOR]
      Sheets("Temp").Delete
   Application.DisplayAlerts = [COLOR=darkblue]True[/COLOR]
 
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

bertie · Apr 3, 2011

Hi Jemma,

I have adjusted the ProcessReOrganizedData procedure as highlighted below:

Code:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] ProcessReOrganizedData()
   [COLOR=darkblue]Dim[/COLOR] wsNew [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] wsTemp [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] rngTemp [COLOR=darkblue]As[/COLOR] Range
   [COLOR=darkblue]Dim[/COLOR] sCurr [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]           [COLOR=green]'currency[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] sBroker [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]         'broker
   [COLOR=darkblue]Dim[/COLOR] rowNew [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] colNew [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] dTotal [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR]
 
   [COLOR=darkblue]Set[/COLOR] wsTemp = Sheets("Temp")
   [COLOR=darkblue]Set[/COLOR] rngTemp = wsTemp.Range("B2")
   sCurr = ""
   sBroker = ""
   rowNew = 1
 
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rngTemp = ""
      [COLOR=green]'do we need to cretae a new worksheet?[/COLOR]
      [COLOR=darkblue]If[/COLOR] sCurr <> rngTemp.Value [COLOR=darkblue]Then[/COLOR]
         sCurr = rngTemp.Value
         Worksheets.Add After:=Sheets("Temp")
         ActiveSheet.Name = sCurr
         [COLOR=darkblue]Set[/COLOR] wsNew = Sheets(sCurr)
         rowNew = 2
         colNew = 1
 
         [COLOR=green]'copy headers[/COLOR]
         wsTemp.Range("A1:D1").Copy Destination:=wsNew.Cells(1, colNew)
         [COLOR=green]'copy values[/COLOR]
         wsTemp.Range("A" & rngTemp.Row & ":D" & rngTemp.Row).Copy _
            Destination:=wsNew.Cells(rowNew, colNew)
 
         [COLOR=green]'reset the broker[/COLOR]
         sBroker = rngTemp.Offset(0, -1).Value
 
      [COLOR=darkblue]Else[/COLOR]
         [COLOR=green]'check the broker[/COLOR]
         [COLOR=darkblue]If[/COLOR] sBroker = rngTemp.Offset(0, -1).Value [COLOR=darkblue]Then[/COLOR]
            rowNew = rowNew + 1
            [COLOR=green]'copy values[/COLOR]
            wsTemp.Range("A" & rngTemp.Row & ":D" & rngTemp.Row).Copy _
            Destination:=wsNew.Cells(rowNew, colNew)
         [COLOR=darkblue]Else[/COLOR]
            [COLOR=green]'move output along five columns[/COLOR]
            rowNew = 2
            colNew = colNew + 5
 
            [COLOR=green]'copy headers[/COLOR]
            wsTemp.Range("A1:D1").Copy Destination:=wsNew.Cells(1, colNew)
            [COLOR=green]'copy values[/COLOR]
            wsTemp.Range("A" & rngTemp.Row & ":D" & rngTemp.Row).Copy _
               Destination:=wsNew.Cells(rowNew, colNew)
 
            [COLOR=green]'reset the broker[/COLOR]
            sBroker = rngTemp.Offset(0, -1).Value
 
          [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
 
     [COLOR=green]'do we need to add total?[/COLOR]
[COLOR=red]     If rngTemp.Offset(0, -1).Value <> rngTemp.Offset(1, -1).Value Then[/COLOR]
 
[COLOR=red]        With wsNew[/COLOR]
[COLOR=red]           .Cells(rowNew + 2, colNew + 1).Value = "Total"[/COLOR]
[COLOR=red]           .Cells(rowNew + 2, colNew + 2).Value = _[/COLOR]
[COLOR=red]              .Application.WorksheetFunction.Sum _[/COLOR]
[COLOR=red]              (Range(.Cells(2, colNew + 2), Cells(rowNew + 1, colNew + 2)))[/COLOR]
[COLOR=red]           .Cells(rowNew + 2, colNew + 2).NumberFormat = "#,##0.00_ ;[Red]#,##0.00"[/COLOR]
[COLOR=red]        End With[/COLOR]
 
[COLOR=red]     End If[/COLOR]
 
      [COLOR=darkblue]Set[/COLOR] rngTemp = rngTemp.Offset(1, 0)
 
      [COLOR=green]'autofit the columns[/COLOR]
      wsNew.Cells.EntireColumn.AutoFit
 
   [COLOR=darkblue]Loop[/COLOR]
 
   [COLOR=darkblue]Set[/COLOR] rngTemp = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wsNew = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wsTemp = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Jemma Atkinson · Apr 3, 2011

Your too good Bertie, thanks heaps for this

bertie said:

Hi Jemma,

I have adjusted the ProcessReOrganizedData procedure as highlighted below:

Code:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] ProcessReOrganizedData()
   [COLOR=darkblue]Dim[/COLOR] wsNew [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] wsTemp [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] rngTemp [COLOR=darkblue]As[/COLOR] Range
   [COLOR=darkblue]Dim[/COLOR] sCurr [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]           [COLOR=green]'currency[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] sBroker [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]         'broker
   [COLOR=darkblue]Dim[/COLOR] rowNew [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] colNew [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] dTotal [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR]
 
   [COLOR=darkblue]Set[/COLOR] wsTemp = Sheets("Temp")
   [COLOR=darkblue]Set[/COLOR] rngTemp = wsTemp.Range("B2")
   sCurr = ""
   sBroker = ""
   rowNew = 1
 
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rngTemp = ""
      [COLOR=green]'do we need to cretae a new worksheet?[/COLOR]
      [COLOR=darkblue]If[/COLOR] sCurr <> rngTemp.Value [COLOR=darkblue]Then[/COLOR]
         sCurr = rngTemp.Value
         Worksheets.Add After:=Sheets("Temp")
         ActiveSheet.Name = sCurr
         [COLOR=darkblue]Set[/COLOR] wsNew = Sheets(sCurr)
         rowNew = 2
         colNew = 1
 
         [COLOR=green]'copy headers[/COLOR]
         wsTemp.Range("A1:D1").Copy Destination:=wsNew.Cells(1, colNew)
         [COLOR=green]'copy values[/COLOR]
         wsTemp.Range("A" & rngTemp.Row & ":D" & rngTemp.Row).Copy _
            Destination:=wsNew.Cells(rowNew, colNew)
 
         [COLOR=green]'reset the broker[/COLOR]
         sBroker = rngTemp.Offset(0, -1).Value
 
      [COLOR=darkblue]Else[/COLOR]
         [COLOR=green]'check the broker[/COLOR]
         [COLOR=darkblue]If[/COLOR] sBroker = rngTemp.Offset(0, -1).Value [COLOR=darkblue]Then[/COLOR]
            rowNew = rowNew + 1
            [COLOR=green]'copy values[/COLOR]
            wsTemp.Range("A" & rngTemp.Row & ":D" & rngTemp.Row).Copy _
            Destination:=wsNew.Cells(rowNew, colNew)
         [COLOR=darkblue]Else[/COLOR]
            [COLOR=green]'move output along five columns[/COLOR]
            rowNew = 2
            colNew = colNew + 5
 
            [COLOR=green]'copy headers[/COLOR]
            wsTemp.Range("A1:D1").Copy Destination:=wsNew.Cells(1, colNew)
            [COLOR=green]'copy values[/COLOR]
            wsTemp.Range("A" & rngTemp.Row & ":D" & rngTemp.Row).Copy _
               Destination:=wsNew.Cells(rowNew, colNew)
 
            [COLOR=green]'reset the broker[/COLOR]
            sBroker = rngTemp.Offset(0, -1).Value
 
          [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
 
     [COLOR=green]'do we need to add total?[/COLOR]
[COLOR=red]     If rngTemp.Offset(0, -1).Value <> rngTemp.Offset(1, -1).Value Then[/COLOR]
 
[COLOR=red]        With wsNew[/COLOR]
[COLOR=red]           .Cells(rowNew + 2, colNew + 1).Value = "Total"[/COLOR]
[COLOR=red]           .Cells(rowNew + 2, colNew + 2).Value = _[/COLOR]
[COLOR=red]              .Application.WorksheetFunction.Sum _[/COLOR]
[COLOR=red]              (Range(.Cells(2, colNew + 2), Cells(rowNew + 1, colNew + 2)))[/COLOR]
[COLOR=red]           .Cells(rowNew + 2, colNew + 2).NumberFormat = "#,##0.00_ ;[Red]#,##0.00"[/COLOR]
[COLOR=red]        End With[/COLOR]
 
[COLOR=red]     End If[/COLOR]
 
      [COLOR=darkblue]Set[/COLOR] rngTemp = rngTemp.Offset(1, 0)
 
      [COLOR=green]'autofit the columns[/COLOR]
      wsNew.Cells.EntireColumn.AutoFit
 
   [COLOR=darkblue]Loop[/COLOR]
 
   [COLOR=darkblue]Set[/COLOR] rngTemp = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wsNew = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wsTemp = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Jemma Atkinson · Apr 11, 2011

Hi Bertie,

I realised that the totals must be a net position of buys and sells, where the sells are negatives, my bad. So when the code totals each quantity can it take into a/c if its a sell then its a negative, so as an example the below should total to 65,716.33 rather than 81,571,868.91.

Excel Workbook

BD

BE

BF

BG

1

Broker

CCY

Quantity

Method

2

WESTPAC BANKING CORPORATI

USD

2,736,670.29

BUY

3

WESTPAC BANKING CORPORATI

USD

1,296,126.32

BUY

4

WESTPAC BANKING CORPORATI

USD

2,328,178.14

BUY

5

WESTPAC BANKING CORPORATI

USD

652,722.56

BUY

6

WESTPAC BANKING CORPORATI

USD

1,455,064.81

BUY

7

WESTPAC BANKING CORPORATI

USD

1,454,781.05

BUY

8

WESTPAC BANKING CORPORATI

USD

1,214,106.64

BUY

9

WESTPAC BANKING CORPORATI

USD

1,482,392.79

BUY

10

WESTPAC BANKING CORPORATI

USD

1,668,418.36

BUY

11

WESTPAC BANKING CORPORATI

USD

13,031,544.96

BUY

12

WESTPAC BANKING CORPORATI

USD

10,830,637.78

BUY

13

WESTPAC BANKING CORPORATI

USD

1,919,720.49

BUY

14

WESTPAC BANKING CORPORATI

USD

682,712.10

BUY

15

WESTPAC BANKING CORPORATI

USD

4,614,606.33

SELL

16

WESTPAC BANKING CORPORATI

USD

11,580,325.62

SELL

17

WESTPAC BANKING CORPORATI

USD

12,125,374.93

SELL

18

WESTPAC BANKING CORPORATI

USD

1,864,441.67

SELL

19

WESTPAC BANKING CORPORATI

USD

2,124,989.32

SELL

20

WESTPAC BANKING CORPORATI

USD

1,445,676.16

SELL

21

WESTPAC BANKING CORPORATI

USD

2,787,275.53

SELL

22

WESTPAC BANKING CORPORATI

USD

2,339,890.36

SELL

23

WESTPAC BANKING CORPORATI

USD

1,310,897.50

SELL

24

WESTPAC BANKING CORPORATI

USD

625,315.20

SELL

25

26

Total

65,716.33

USD

bertie said:

Hi Jemma,

I have adjusted the ProcessReOrganizedData procedure as highlighted below:

Code:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] ProcessReOrganizedData()
   [COLOR=darkblue]Dim[/COLOR] wsNew [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] wsTemp [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] rngTemp [COLOR=darkblue]As[/COLOR] Range
   [COLOR=darkblue]Dim[/COLOR] sCurr [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]           [COLOR=green]'currency[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] sBroker [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]         'broker
   [COLOR=darkblue]Dim[/COLOR] rowNew [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] colNew [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] dTotal [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR]
 
   [COLOR=darkblue]Set[/COLOR] wsTemp = Sheets("Temp")
   [COLOR=darkblue]Set[/COLOR] rngTemp = wsTemp.Range("B2")
   sCurr = ""
   sBroker = ""
   rowNew = 1
 
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rngTemp = ""
      [COLOR=green]'do we need to cretae a new worksheet?[/COLOR]
      [COLOR=darkblue]If[/COLOR] sCurr <> rngTemp.Value [COLOR=darkblue]Then[/COLOR]
         sCurr = rngTemp.Value
         Worksheets.Add After:=Sheets("Temp")
         ActiveSheet.Name = sCurr
         [COLOR=darkblue]Set[/COLOR] wsNew = Sheets(sCurr)
         rowNew = 2
         colNew = 1
 
         [COLOR=green]'copy headers[/COLOR]
         wsTemp.Range("A1:D1").Copy Destination:=wsNew.Cells(1, colNew)
         [COLOR=green]'copy values[/COLOR]
         wsTemp.Range("A" & rngTemp.Row & ":D" & rngTemp.Row).Copy _
            Destination:=wsNew.Cells(rowNew, colNew)
 
         [COLOR=green]'reset the broker[/COLOR]
         sBroker = rngTemp.Offset(0, -1).Value
 
      [COLOR=darkblue]Else[/COLOR]
         [COLOR=green]'check the broker[/COLOR]
         [COLOR=darkblue]If[/COLOR] sBroker = rngTemp.Offset(0, -1).Value [COLOR=darkblue]Then[/COLOR]
            rowNew = rowNew + 1
            [COLOR=green]'copy values[/COLOR]
            wsTemp.Range("A" & rngTemp.Row & ":D" & rngTemp.Row).Copy _
            Destination:=wsNew.Cells(rowNew, colNew)
         [COLOR=darkblue]Else[/COLOR]
            [COLOR=green]'move output along five columns[/COLOR]
            rowNew = 2
            colNew = colNew + 5
 
            [COLOR=green]'copy headers[/COLOR]
            wsTemp.Range("A1:D1").Copy Destination:=wsNew.Cells(1, colNew)
            [COLOR=green]'copy values[/COLOR]
            wsTemp.Range("A" & rngTemp.Row & ":D" & rngTemp.Row).Copy _
               Destination:=wsNew.Cells(rowNew, colNew)
 
            [COLOR=green]'reset the broker[/COLOR]
            sBroker = rngTemp.Offset(0, -1).Value
 
          [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
 
     [COLOR=green]'do we need to add total?[/COLOR]
[COLOR=red]     If rngTemp.Offset(0, -1).Value <> rngTemp.Offset(1, -1).Value Then[/COLOR]
 
[COLOR=red]        With wsNew[/COLOR]
[COLOR=red]           .Cells(rowNew + 2, colNew + 1).Value = "Total"[/COLOR]
[COLOR=red]           .Cells(rowNew + 2, colNew + 2).Value = _[/COLOR]
[COLOR=red]              .Application.WorksheetFunction.Sum _[/COLOR]
[COLOR=red]              (Range(.Cells(2, colNew + 2), Cells(rowNew + 1, colNew + 2)))[/COLOR]
[COLOR=red]           .Cells(rowNew + 2, colNew + 2).NumberFormat = "#,##0.00_ ;[Red]#,##0.00"[/COLOR]
[COLOR=red]        End With[/COLOR]
 
[COLOR=red]     End If[/COLOR]
 
      [COLOR=darkblue]Set[/COLOR] rngTemp = rngTemp.Offset(1, 0)
 
      [COLOR=green]'autofit the columns[/COLOR]
      wsNew.Cells.EntireColumn.AutoFit
 
   [COLOR=darkblue]Loop[/COLOR]
 
   [COLOR=darkblue]Set[/COLOR] rngTemp = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wsNew = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wsTemp = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Challenging Macro help

Jemma Atkinson

Well-known Member

Excel Facts

bertie

Well-known Member

Jemma Atkinson

Well-known Member

bertie

Well-known Member

bertie

Well-known Member

Jemma Atkinson

Well-known Member

Jemma Atkinson

Well-known Member

bertie

Well-known Member

Jemma Atkinson

Well-known Member

Jemma Atkinson

Well-known Member

Similar threads

Forum statistics

Share this page

Challenging Macro help

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

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