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

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
JustinFinancial ControlAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
What does the sub/function ImportMultipleTextFiles do?
JustinFinancial ControlAuthor 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?
Determine the Perfect Price for Your IT Services

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

JustinFinancial ControlAuthor 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.
NorieAnalyst 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JustinFinancial ControlAuthor 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.