troubleshooting Question

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

Avatar of Justin
Justin asked on
Microsoft OfficeVisual Basic ClassicMicrosoft Excel
6 Comments1 Solution183 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
NorieAnalyst Assistant
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros