Very slow Macro, need help speeding it up

J

jrwrita

Board Regular
Joined
May 7, 2015
Messages
206
Hi,

Below is my code, is there anyone who can help optimize this?


Code: 
Sub filtering()
'


' filtering Macro
'



    Dim Rng1 As Range, Rng2 As Range
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
        Dim src As Range

    Set ws1 = ThisWorkbook.Sheets("eodcpos")
    Set ws2 = ThisWorkbook.Sheets("valumeasure3")
    Set ws3 = ThisWorkbook.Sheets("File")

    Set ws4 = ThisWorkbook.Sheets.Add
    ws4.Name = "lookup"
    
    Set ws5 = ThisWorkbook.Sheets("Sample File")
    
    Set Rng1 = ws1.UsedRange
    Set Rng2 = ws2.UsedRange
    Set src = Worksheets("File").Range("2:757")
    Set src1 = Worksheets("lookup").Range("2:17783")
    Set src2 = Worksheets("Sample File").Range("2:757")

'
   Rng1.AutoFilter Field:=11, Criteria1:= _
        "=Traded Position"
        
  Rng1.AutoFilter Field:=2, Criteria1:= _
        "<>*-C*", Operator:=xlAnd, Criteria2:="<>*-P*"
    
    Rng1.AutoFilter Field:=109, Criteria1:=Array _
        ("Foreign Exchange Forward", "Foreign Exchange Spot", "Foreign Exchange Swap"), _
        Operator:=xlFilterValues
              
    Rng1.AutoFilter Field:=33, Criteria1:= _
        "<>NA"
       
    Rng1.AutoFilter Field:=63, Criteria1:= _
        "<>129540", Operator:=xlAnd, Criteria2:="<>135845"
         
    Rng2.AutoFilter Field:=5, Criteria1:= _
        "=Buy Notional Amount", Operator:=xlOr, Criteria2:="=Sell Notional Amount"
    
    '' vlookup file
    
        ws4.Activate
        
        Range("A1").Value = "val pos id"
        ws2.Columns(3).Copy Destination:=Sheets("lookup").Columns(1)
        ws4.Range("A:A").RemoveDuplicates Columns:=Array(1)
        
        Range("B1").Value = "eodc pos id"
        ws1.Columns(2).Copy Destination:=Sheets("lookup").Columns(2)
        Range("C1").Value = "eodc pos decor id"
        ws1.Columns(41).Copy Destination:=Sheets("lookup").Columns(3)
        
        Range("D1").Value = "pos id lookup"
        Range("D2").Select
        ActiveCell = "=VLOOKUP(A2,B:B,1,FALSE)"
        Selection.AutoFill Destination:=src1.Columns("D")
        
        
        Range("E1").Value = "pos decor id lookup"
        Range("E2").Select
        ActiveCell = "=VLOOKUP(fxpd!B2,C:C,1,FALSE)"
        Selection.AutoFill Destination:=src1.Columns("E")
        
        ''Filtering File data
        ws4.UsedRange.AutoFilter Field:=4, Criteria1:= _
        "<>#N/A"
       
       ''creating File tab
        ws4.Columns(4).Copy Destination:=Sheets("File").Columns(1)  ''copy and paste filtered values from valuation measure file
        ws4.Columns(5).Copy Destination:=Sheets("File").Columns(2)  ''  copy and paste filtered values from fxpd
        
        ws3.Activate
        
        
        Range("X2").Select
        ActiveCell = "=VLOOKUP(A2,eodcpos!B:BP,COLUMNS(B:BP),FALSE)"   ''product classification code
        Selection.AutoFill Destination:=src.Columns("X")
        
     '' ActiveCell.FormulaR1C1 = _
     ''   "=IF(RIGHT(LEFT(RC[20],64),40)=""ProductType:'FXD';ProductSubType:'SWLEG'"",""XSW"",IF(RIGHT(LEFT(RC[20],64),40)=""ProductType:'FXD';ProductSubType:'FXD'"",""FXD"",""NA""))"
     ''  Range("D2").Select
     ''   Selection.AutoFill Destination:=src.Columns("D")
    
        Range("D2").Select
        ActiveCell = "=IF(RIGHT(LEFT(X2,64),40)=""ProductType:'FXD';ProductSubType:'SWLEG'"",""XSW"",IF(RIGHT(LEFT(X2,64),38)=""ProductType:'FXD';ProductSubType:'FXD'"",""FXD"",""NA""))"
        Selection.AutoFill Destination:=src.Columns("D")
        
        Range("E2").Select
        ActiveCell = "=VLOOKUP(A2,eodcpos!B:BK,62,FALSE)"   ''counterparty short name  / client id
        Selection.AutoFill Destination:=src.Columns("E")
        Range("F2").Select
        ActiveCell = "=VLOOKUP(A2,eodcpos!B:BK,17,FALSE)"  ''source trade id  / deal id
        Selection.AutoFill Destination:=src.Columns("F")
        Range("G2").Select
        ActiveCell = "=VLOOKUP(A2,eodcpos!B:BK,27,FALSE)"   ''trade date / contract date
        Selection.AutoFill Destination:=src.Columns("G")
        Range("H2").Select
        ActiveCell = "=VLOOKUP(A2,eodcpos!B:V,COLUMNS(B:V),FALSE)"   ''settlement date / actual settlement date
        Selection.AutoFill Destination:=src.Columns("H")
        Range("I2").Select
        ActiveCell = "=VLOOKUP(A2,eodcpos!B:BG,COLUMNS(B:BG),FALSE)"   ''book runner / source book name
        Selection.AutoFill Destination:=src.Columns("I")

''lookup into fxpd now

          Range("J2").Select
        ActiveCell = "=VLOOKUP(A2,eodcpos!B:AO,COLUMNS(B:AO),FALSE)"   ''pull in pos decorator id
        Selection.AutoFill Destination:=src.Columns("J")
             Range("K2").Select
        ActiveCell = "=VLOOKUP(B2,fxpd!B:U,COLUMNS(B:U),FALSE)"   ''spot rate / forward rate
        Selection.AutoFill Destination:=src.Columns("K")
             Range("L2").Select
        ActiveCell = "=VLOOKUP(B2,fxpd!B:U,COLUMNS(B:U),FALSE)"
        Selection.AutoFill Destination:=src.Columns("L")        ''outright rate / forward rate
            
            Range("M2").Select
        ActiveCell = "=VLOOKUP(B2,fxpd!B:I,COLUMNS(B:I),FALSE)"   ''buy currency code / buy curency
        Selection.AutoFill Destination:=src.Columns("M")
         Range("N2").Select
        ActiveCell = "=VLOOKUP(B2,fxpd!B:AK,COLUMNS(B:AK),FALSE)"   ''sell currency code / sell curency
        Selection.AutoFill Destination:=src.Columns("N")
        
        
        

        src.Columns("O") = "LIVE"  ''hardcode type name
        src.Columns("P") = "1"  ''hardcode leg number
        src.Columns("Q") = "S"  ''hardcode leg duration code
        src.Columns("R") = ""  ''hardcode option value date



         Range("S2").Select
       '' ActiveCell = "=IF(valumeasure3!E2=""Buy Notional Amount"",VLOOKUP(A2,valumeasure3!C:U, COLUMNS(C:U),FALSE),0)"
        ActiveCell = "=SUMIFS(valumeasure3!U:U,valumeasure3!C:C,File!A2,valumeasure3!E:E,""Buy Notional Amount"")"    ''buy currency amt
        Selection.AutoFill Destination:=src.Columns("S")
        
        
          Range("T2").Select
       '' ActiveCell = "=IF(valumeasure3!E2=""Sell Notional Amount"",VLOOKUP(A2,valumeasure3!C:U, COLUMNS(C:U),FALSE),0)"
        ActiveCell = "=SUMIFS(valumeasure3!U:U,valumeasure3!C:C,File!A2,valumeasure3!E:E,""Sell Notional Amount"")"  ''sell curency amt
        Selection.AutoFill Destination:=src.Columns("T")
        
            Range("U2").Select
        ActiveCell = "=VLOOKUP(A2,eodcpos!B:I,8,FALSE)"   ''setup for buy risk currency flag
        Selection.AutoFill Destination:=src.Columns("U")

         
            Range("V2").Select
        ActiveCell = "=IF(U2=""Long"",""B"",""S"")"   ''buy risk currency flag
        Selection.AutoFill Destination:=src.Columns("V")
        
         Range("W2").Select
        ActiveCell = "=IF(S2<>""0"",T2/S2,""0"")"   ''
        Selection.AutoFill Destination:=src.Columns("W")

       

''headers
        Range("C1").Value = ""
         Range("D1").Value = "Product Family Name"
           Range("E1").Value = "Client name"
              Range("F1").Value = "deal ID"
                 Range("G1").Value = "trade Date"
                    Range("H1").Value = "settlement"
                       Range("I1").Value = "source book"
                          Range("J1").Value = "PD ID"
                             Range("K1").Value = "forward rate"
                                Range("L1").Value = "forward rate"
                                 Range("M1").Value = "buy currency"
                                        Range("N1").Value = "sell currency"
                                           Range("O1").Value = "type name"
                                               Range("P1").Value = "leg number"
                                                   Range("Q1").Value = "duration"
                                                           Range("R1").Value = "option value date"
                                                                Range("S1").Value = "buy ccy amt"
                                                                         Range("T1").Value = "sell ccy amt"
                                                                            Range("U1").Value = "N/A"
                                                                            Range("V1").Value = "buy risk ccy flag"
                                                                            Range("W1").Value = "sell buy ratio"
                                                                            

   ws5.Activate
       
            
                
     
     
     ws3.Columns(4).Copy
        Sheets("Sample File").Columns(1).PasteSpecial xlPasteValues
         ws3.Columns(5).Copy
        Sheets("Sample File").Columns(2).PasteSpecial xlPasteValues
            ws3.Columns(6).Copy
        Sheets("Sample File").Columns(3).PasteSpecial xlPasteValues
        src2.Columns("D") = "1"
           ws3.Columns(7).Copy
        Sheets("Sample File").Columns(5).PasteSpecial xlPasteValues
          ws3.Columns(8).Copy
        Sheets("Sample File").Columns(6).PasteSpecial xlPasteValues
           ws3.Columns(9).Copy
        Sheets("Sample File").Columns(7).PasteSpecial xlPasteValues
               src2.Columns("H") = "LIVE"  ''hardcode type name
        src2.Columns("I") = "1"  ''hardcode leg number
        src2.Columns("J") = "S"  ''hardcode leg duration code
           ws3.Columns(11).Copy
        Sheets("Sample File").Columns(11).PasteSpecial xlPasteValues
        ws3.Columns(11).Copy
        Sheets("Sample File").Columns(12).PasteSpecial xlPasteValues
        ws3.Columns(13).Copy
        Sheets("Sample File").Columns(13).PasteSpecial xlPasteValues
        ws3.Columns(14).Copy
        Sheets("Sample File").Columns(14).PasteSpecial xlPasteValues
        ws3.Columns(19).Copy
        Sheets("Sample File").Columns(15).PasteSpecial xlPasteValues
          ws3.Columns(20).Copy
        Sheets("Sample File").Columns(16).PasteSpecial xlPasteValues
          ws3.Columns(22).Copy
        Sheets("Sample File").Columns(17).PasteSpecial xlPasteValues
       src2.Columns("R") = "" ''hardcode option value date
         ws3.Columns(23).Copy
        Sheets("Sample File").Columns(19).PasteSpecial xlPasteValues
        
         'headers for Sample File sheet
     Range("A1").Value = "Product Family Name"
     Range("B1").Value = "Client Name"
        Range("C1").Value = "Deal ID"
         Range("D1").Value = "Amendment Number"
           Range("E1").Value = "Trade Date"
              Range("F1").Value = "Settlement Date"
                 Range("G1").Value = "Book Runner"
                    Range("H1").Value = "Leg Status Type Name"
                       Range("I1").Value = "Leg Number"
                          Range("J1").Value = "Leg Duration Code"
                             Range("K1").Value = "Spot Rate"
                                Range("L1").Value = "Outright Rate"
                                 Range("M1").Value = "Buy Currency Code"
                                        Range("N1").Value = "Sell Currency Code"
                                           Range("O1").Value = "Buy Currency Amt"
                                               Range("P1").Value = "Sell Currency Amt"
                                                   Range("Q1").Value = "Buy Risk Currency Flag"
                                                           Range("R1").Value = "Option Value Date"
                                                            Range("S1").Value = "Sell Buy Ratio"
       
   End Sub
 
Sort by date Sort by votes
There is no conflict, it is just if you have no Event code then there is no point turning Events off and on (if you have no other code then you don't have any) and in your case turning calculations off during the code will make a difference, as will turning off screenupdating.

I hope that is clearer.
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
You must log in or register to reply here.

Similar threads

N
  • Question Question
My code is suddenly really slow
Replies
1
Views
369
RobP
R
I
  • Solved
Current Code taking 10 Hours to run. Need Help to Optimize
Replies
5
Views
512
ItalianPlatinum
I
L
  • Locked
  • Question Question
Help Cleaning Up Code
Replies
2
Views
584
RoryA
RoryA
M
  • Question Question
Cleaning up Code
Replies
0
Views
796
MHamid
M
B
  • Question Question
Need help with VBA sumif function
Replies
1
Views
640
sijpie
sijpie

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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