Avatar of Justin
Justin
 asked on

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
Microsoft ExcelVisual Basic ClassicMicrosoft Office

Avatar of undefined
Last Comment
Justin

8/22/2022 - Mon
Norie

What does the sub/function ImportMultipleTextFiles do?
Justin

ASKER
It opens a delimited text file (like the Text to Columns function) and formats it.
Norie

Is it that code that's slowing things down?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Justin

ASKER
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.
ASKER CERTIFIED SOLUTION
Norie

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Justin

ASKER
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.