Shortening and speeding up a data extract macro

[Bobstar]

Hi all

I monitor a range of portfolios and each quarter; I get performance data of the portfolios from an external provider. For each portfolio, I get 4 sets of data. These files/workbooks are then saved in a folder.
I have created a dashboard that summarises these data in charts. The dashboard allows a user to input the portfolio name and path of the files and fetches the data.

In summary, the process is as follows:

1. User selects/enters portfolio name (cell “F6”) and file path (cell “J6”) where the source files are stored and clicks a button to fetch the data.

2. Macro opens the four source files and copies the data in sheet1 (“Attribution”) to different sheets in the Masterfile/dashboard.

3. Using portfolio 1 as an example, macro copies data as follows:

• Data in sheet 1 of Portfolio1 vs BM(1) – level 1a workbook is copied to sheet(“BM(1)_level1a”)
• Data in sheet 1 of Portfolio1 vs BM(1) – level 1b workbook is copied to sheet(“BM(1)_level1b”)
• Data in sheet 1 of Portfolio1 vs BM(2) – level 1a workbook is copied to sheet(“BM(2)_level1a”)
• Data in sheet 1 of Portfolio1 vs BM(2) – level 1b workbook is copied to sheet(“BM(2)_level1b”)

The following macro does the job, but I am looking for ideas on how to simplify it so that it can run faster. At the moment it is taking around 8-10 seconds.

Thanks

 

[offthelip]

The first thing I would try is just using the same code but reordering the code so that you only deal with one workbook at a time , ie. open the workbook , then determine the last row, then copy the data then close the workbook. then do the next workbook. This will save EXCEL needing to continually swap between workbooks on every step of your code.. Also it might reduce the amount of memory needed

 

[Bobstar]

The first thing I would try is just using the same code but reordering the code so that you only deal with one workbook at a time , ie. open the workbook , then determine the last row, then copy the data then close the workbook. then do the next workbook. This will save EXCEL needing to continually swap between workbooks on every step of your code.. Also it might reduce the amount of memory needed

Thanks I will give it a try.

 

[Bobstar]

The first thing I would try is just using the same code but reordering the code so that you only deal with one workbook at a time , ie. open the workbook , then determine the last row, then copy the data then close the workbook. then do the next workbook. This will save EXCEL needing to continually swap between workbooks on every step of your code.. Also it might reduce the amount of memory needed

Tried this approach. Processing time has nearly doubled. From 8-10 seconds to 16 seconds ?