Excel with macro taking very long to open up

Hello Everyone,
This is my first post here.

I have an Excel which executes the following code when it is opened-

Please Login or Register  to view this content.

Each macro is fetching data from different excel and pasting it in current excel. I have already optimized most of them but the excel is still taking close to 30 seconds to open.

I was thinking following Macro6 is the problem but I can't find a way to further optimize it. Adding sample code below-

Please Login or Register  to view this content.

Any suggestions would be appreciated.

Thank you!

Did you put any monitoring in, like, for example, to see how long it takes to open whatever workbook is targeted in the code? If that is taking a long time, then you are not going to be able to speed it up in the macro.

I actually did not. I am pretty new to this. but thanks for the suggestion. I will try to implement this.

Originally Posted by GlennUK

Did you put any monitoring in, like, for example, to see how long it takes to open whatever workbook is targeted in the code? If that is taking a long time, then you are not going to be able to speed it up in the macro.

I actually did not. I am pretty new to this. but thanks for the suggestion. I will try to implement this.

Originally Posted by TMS

From a diagnostic perspective, you could use Debug.Print to display the current time and/or calculate the duration of each stage of the process. That might isolate lengthy processing times.

In terms of improvement, two options spring to mind. One, you could use a Helper column with the formula =MOD(ROW(), 2) and filter on the Helper column. Copy visible rows and paste the values, then delete the Helper column. Or, two, you could read the range into an array, process the array, copying every other cell to a second array, then post the second array to the output sheet.

I liked both of your options for improvement. In theory, the helper column method looks easier. I will try to implement that. Thank you for your suggestion.

You might try hiding the alternating columns in one loop, and then copying only the visible cells as needed, and unhiding when done.