We help IT Professionals succeed at work.

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

178 Views
Last Modified: 2018-10-25
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

NorieAnalyst Assistant
CERTIFIED EXPERT

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
CERTIFIED EXPERT

Commented:
Is it that code that's slowing things down?
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
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
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.