Excel Macro takes 10 minutes to run - importing Friday, Saturday and Sunday dated text files into Excel. How to make  it run faster?

Justin
Justin used Ask the Experts™
on
Hi Guys, on Mondays I have to import Dated files of our companys Trial Balance as of Friday, Saturday & Sunday within Excel. I have thus far ran a Macro for each separate date to import each dated file, so Fridays data is imported first , then Saturdays data is copied and appended below Fridays data then Sundays data is copied and appended below Sundays data. I have tried creating just 1 macro to import all 3 dated files but it takes 10 minutes to run versus 1 minute to run each individually. Why?
Can anyone suggest a way to make it faster?  Here's the code:


Sub FXweekend ()

Dim filename0 as string
Dim target , target1 , target 2 as variant

Sheets ("Setup"). Activate

filepath0 = Activesheet.Range ("filepath0")
filrname0 = Activeworkbook.Name
filePath3 = ActiveSheet.Range ("filePath3")
filename3 = Activesheet.Range ("filename3")
SaturdayFX = Activesheet. Range ("SaturdayFX")
FridayFX = Activesheet. Range ("FridayFX")

ImportMultipleTextFiles filePath3 & filename3, "Murex_FX_PnL"




Range ("A1").Select
Rows.Autofilter Field:=4, Criteria :=*<>_LN", Operator :xlAnd, Criteria2:="<>*WH*"
Range.(Selection, Activecell.SpecialCells (xlLastCell)). Select
Selection.Copy
Windows (filename0). Activate
Sheets ("Murex_FX_Pnl"). Select
Range ("C20").Select
Selection.PasteSpecial Paste =xlPasteValues, Opeartion :=xlNone, SkipBlanks:=False, Transpose : = False


Range ("A1").Select
Rows.Autofilter Field:=4, Criteria :=*<>_LN", Operator :xlAnd, Criteria2:="<>*WH*"
Range.(Selection, Activecell.SpecialCells (xlLastCell)). Select
Selection.Copy
Windows (filename0). Activate
Sheets ("Murex_FX_Pnl"). Select

Settarget1 = Sheets ("Murex_FX_Pnl"). Range ("C20").End (xlDown).Offset (1)
target1.Pastespecial xlPasteValues

Range ("A1").Select
Rows.Autofilter Field:=4, Criteria :=*<>_LN", Operator :xlAnd, Criteria2:="<>*WH*"
Range.(Selection, Activecell.SpecialCells (xlLastCell)). Select
Selection.Copy
Windows (filename0). Activate
Sheets ("Murex_FX_Pnl"). Select
Set target2 = Sheets (Murex_FX_Pnl"). range ("C20").End (XlDown).Offset (1)


End Sub










Import
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

Commented:
What does the sub/function ImportMultipleTextFiles do?
JustinFinancial Control

Author

Commented:
It opens a delimited text file (like the Text to Columns function) and formats it.
NorieAnalyst Assistant

Commented:
Is it that code that's slowing things down?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

JustinFinancial Control

Author

Commented:
No, I originally had 1 macro which I ran 3 times for Friday, Saturday and Sunday, I ran one with Fridays date, changed the cell to Saturday date then pushed it, then changed cell to Sundays date and then pushed it. I amalgamated  into 1 macro which would import each day one after the other with only 1 push of the macro.
Analyst Assistant
Commented:
I'm confused, isn't ImportMultipleTextFiles the part that actually does the import?

If it is I would have thought that would be the most likely culprit for slowing things down.

Also, the rest of the code looks pretty straightforward though you could speed it up by turning off screen updating, setting calculation to manual etc - see below.
Sub FXweekend()
Dim filename0 As String
Dim target, target1, target2 As Variant

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    
    Sheets("Setup").Activate

    filepath0 = ActiveSheet.Range("filepath0")
    filrname0 = ActiveWorkbook.Name
    filePath3 = ActiveSheet.Range("filePath3")
    filename3 = ActiveSheet.Range("filename3")
    SaturdayFX = ActiveSheet.Range("SaturdayFX")
    FridayFX = ActiveSheet.Range("FridayFX")

    ImportMultipleTextFiles filePath3 & filename3, "Murex_FX_PnL"

    Range("A1").Select

    Rows.AutoFilter Field:=4, Criteria:="*<>_LN", Operator:=xlAnd, Criteria2:="<>*WH*"
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Windows(filename0).Activate
    Sheets("Murex_FX_Pnl").Select
    Range("C20").Select
    Selection.PasteSpecial Paste = xlPasteValues, Opeartion:=xlNone, SkipBlanks:=False, Transpose:=False


    Range("A1").Select
    Rows.AutoFilter Field:=4, Criteria:="*<>_LN", Operator:=xlAnd, Criteria2:="<>*WH*"
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy

    Windows(filename0).Activate
    Sheets("Murex_FX_Pnl").Select

    Settarget1 = Sheets("Murex_FX_Pnl").Range("C20").End(xlDown).Offset(1)
    target1.PasteSpecial xlPasteValues

    Range("A1").Select
    Rows.AutoFilter Field:=4, Criteria:="*<>_LN", Operator:=xlAnd, Criteria2:="<>*WH*"
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Windows(filename0).Activate
    Sheets("Murex_FX_Pnl").Select
    Set target2 = Sheets("Murex_FX_Pnl").Range("C20").End(xlDown).Offset(1)
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True

End Sub

Open in new window

JustinFinancial Control

Author

Commented:
No, the ImportTextFiles, just opens a text file, does a  text-to-columns  so the columns are in the same format as Excel.

The data is copied, and pasted into an Excel file once out of that function. The ImportTextFiles is in the original macro which works fine when I am importing a single days data.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial