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 · Apr 11, 2011

When we reorganise the data into the temp sheet just enter a negative value. See highlighted line below.

Code:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] [COLOR=red]ReOrganizeData[/COLOR]()
   [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 = [COLOR=red]-(rng.Offset(, -10).Value) [/COLOR][COLOR=seagreen]'col [/COLOR][COLOR=green]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=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]

Jemma Atkinson · Apr 12, 2011

Hi Bertie, after the code has summed up the totals i need the SELL to be Absolute values again this is because when i upload the figures into our reconciliation system it will only accept absolute values

bertie said:

When we reorganise the data into the temp sheet just enter a negative value. See highlighted line below.

Code:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] [COLOR=red]ReOrganizeData[/COLOR]()
   [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 = [COLOR=red]-(rng.Offset(, -10).Value) [/COLOR][COLOR=seagreen]'col [/COLOR][COLOR=green]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=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]

bertie · Apr 12, 2011

Hi Jemma,

Here are the amendments I have made. I have created a function to calculate a RunningTotal.

Code:

[COLOR=darkblue]Function[/COLOR] RunningTotal([COLOR=darkblue]ByRef[/COLOR] dTotal [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR], _
                     [COLOR=darkblue]ByVal[/COLOR] rngTemp [COLOR=darkblue]As[/COLOR] Range) As [COLOR=darkblue]Double[/COLOR]
   [COLOR=green]'calculate running total[/COLOR]
   [COLOR=darkblue]If[/COLOR] rngTemp.Offset(, 2).Value = "BUY" [COLOR=darkblue]Then[/COLOR]
      RunningTotal = dTotal + rngTemp.Offset(, 1).Value
   [COLOR=darkblue]Else[/COLOR]
      RunningTotal = dTotal - rngTemp.Offset(, 1).Value
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Function[/COLOR]

This function is called at various stages in the ProcessReOrganizedData procedure.

I have highlighted below the changes I have made to this procedure.

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=red]  Dim dTotal As Double[/COLOR]
 
   [COLOR=darkblue]Set[/COLOR] wsTemp = Sheets("Temp")
   [COLOR=darkblue]Set[/COLOR] rngTemp = wsTemp.Range("B2")
   sCurr = ""
   sBroker = ""
   rowNew = 1
   dTotal = 0#    [COLOR=green]'0.00[/COLOR]
 
   [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=green]'calculate running total[/COLOR]
[COLOR=red]     dTotal = RunningTotal(dTotal, rngTemp)[/COLOR]
 
      [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=green]'calculate running total[/COLOR]
[COLOR=red]        dTotal = RunningTotal(dTotal, rngTemp)[/COLOR]
         [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=green]'calculate running total[/COLOR]
[COLOR=red]        dTotal = RunningTotal(dTotal, rngTemp)[/COLOR]
 
          [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
 
     [COLOR=green]'Do we need to add total? Check broker and currency.[/COLOR]
      [COLOR=darkblue]If[/COLOR] rngTemp.Offset(0, -1).Value <> rngTemp.Offset(1, -1).Value [COLOR=red]Or _[/COLOR]
[COLOR=red]     rngTemp.Value <> rngTemp.Offset(1, 0).Value[/COLOR] [COLOR=darkblue]Then[/COLOR]
 
         [COLOR=darkblue]With[/COLOR] wsNew
            .Cells(rowNew + 2, colNew + 1).Value = "Total"
            .Cells(rowNew + 2, colNew + 2).Value =[COLOR=red] dTotal[/COLOR]
            .Cells(rowNew + 2, colNew + 2).NumberFormat = "#,##0.00_ ;[Red]#,##0.00"
         [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
         [COLOR=green]'reset the running total[/COLOR]
[COLOR=red]     dTotal = 0#[/COLOR]
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]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]

Replace the ProcessReOrganizedData procedure with the new version and add the function to the same module which contains the rest of the code.

Bertie.

Jemma Atkinson · Apr 12, 2011

Fantastic Bertie, you are a champ! Thanks for all your help on this task.

bertie said:

Hi Jemma,

Here are the amendments I have made. I have created a function to calculate a RunningTotal.

Code:

[COLOR=darkblue]Function[/COLOR] RunningTotal([COLOR=darkblue]ByRef[/COLOR] dTotal [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR], _
                     [COLOR=darkblue]ByVal[/COLOR] rngTemp [COLOR=darkblue]As[/COLOR] Range) As [COLOR=darkblue]Double[/COLOR]
   [COLOR=green]'calculate running total[/COLOR]
   [COLOR=darkblue]If[/COLOR] rngTemp.Offset(, 2).Value = "BUY" [COLOR=darkblue]Then[/COLOR]
      RunningTotal = dTotal + rngTemp.Offset(, 1).Value
   [COLOR=darkblue]Else[/COLOR]
      RunningTotal = dTotal - rngTemp.Offset(, 1).Value
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Function[/COLOR]

This function is called at various stages in the ProcessReOrganizedData procedure.

I have highlighted below the changes I have made to this procedure.

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=red]  Dim dTotal As Double[/COLOR]
 
   [COLOR=darkblue]Set[/COLOR] wsTemp = Sheets("Temp")
   [COLOR=darkblue]Set[/COLOR] rngTemp = wsTemp.Range("B2")
   sCurr = ""
   sBroker = ""
   rowNew = 1
   dTotal = 0#    [COLOR=green]'0.00[/COLOR]
 
   [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=green]'calculate running total[/COLOR]
[COLOR=red]     dTotal = RunningTotal(dTotal, rngTemp)[/COLOR]
 
      [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=green]'calculate running total[/COLOR]
[COLOR=red]        dTotal = RunningTotal(dTotal, rngTemp)[/COLOR]
         [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=green]'calculate running total[/COLOR]
[COLOR=red]        dTotal = RunningTotal(dTotal, rngTemp)[/COLOR]
 
          [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
 
     [COLOR=green]'Do we need to add total? Check broker and currency.[/COLOR]
      [COLOR=darkblue]If[/COLOR] rngTemp.Offset(0, -1).Value <> rngTemp.Offset(1, -1).Value [COLOR=red]Or _[/COLOR]
[COLOR=red]     rngTemp.Value <> rngTemp.Offset(1, 0).Value[/COLOR] [COLOR=darkblue]Then[/COLOR]
 
         [COLOR=darkblue]With[/COLOR] wsNew
            .Cells(rowNew + 2, colNew + 1).Value = "Total"
            .Cells(rowNew + 2, colNew + 2).Value =[COLOR=red] dTotal[/COLOR]
            .Cells(rowNew + 2, colNew + 2).NumberFormat = "#,##0.00_ ;[Red]#,##0.00"
         [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
         [COLOR=green]'reset the running total[/COLOR]
[COLOR=red]     dTotal = 0#[/COLOR]
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]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]

Replace the ProcessReOrganizedData procedure with the new version and add the function to the same module which contains the rest of the code.

Bertie.

Challenging Macro help

Jemma Atkinson

Well-known Member

bertie

Well-known Member

Excel Facts

Jemma Atkinson

Well-known Member

bertie

Well-known Member

Jemma Atkinson

Well-known Member

Similar threads

Forum statistics

Share this page

Challenging Macro help

Jemma Atkinson

Well-known Member

bertie

Well-known Member

Excel Facts

Jemma Atkinson

Well-known Member

bertie

Well-known Member

Jemma Atkinson

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