HELP! How to make code run faster. It's so slow!!

[Skinnymermaid]

Hi,

So here's the story: I have 24 workbooks, with 12 sheets in each workbook. I have a macro written for EACH sheet in EACH workbook. I also have code written in the first module of EACH workbook. These 24 workbooks are in a folder.

I then have another workbook called "Export" which is not in the same folder as the workbooks. I have one macro in the module section of the "Export" workbook which accesses the folder with the 24 workbooks, copies the data from EACH sheet of EACH workbook and then pastes the data into "Export".

My problem is that each time the macro in "Export" opens each workbook it opens each macro in each workbook and it really slows things down (since EACH sheet of EACH workbook contains a macro + a macro in the module of EACH workbook). That's a lot of macros to open. Is there any way it doesn't have to open all these macros so the code can run faster?

Thanks for any help!!!

 

[Fazza]

Hi,

If the data is set up in tables - like a database - then just query the data. So, just query the 24 files x 12 worksheets without opening the data files. Such as using ADO and SQL. Searching will find some examples. Again, this will only be successful if the data is set up like database tables on the source worksheets. What do you think?

Regards, Fazza

 

[Skinnymermaid]

Hi,

If the data is set up in tables - like a database - then just query the data. So, just query the 24 files x 12 worksheets without opening the data files. Such as using ADO and SQL. Searching will find some examples. Again, this will only be successful if the data is set up like database tables on the source worksheets. What do you think?

Regards, Fazza

Thanks Fazza. I guess I would have to use Access for that? However, I need to set up one data sheet with all the data from the 24 files X 12 worksheets. Is that possible in Access?

Otherwise I'm thinking I'll just use the macros to populate the 24 files X 12 worksheets and then just delete the macros.

But is there any way to open the data files WITHOUT opening the macros?


Thanks again.

 

[Fazza]

Access is not required, though is equally an option: it can be done solely using Excel. One worksheet can be populated from the 24 files x 12 worksheets without opening the source data files - easiest if the data in the source files is set up in well structured tables of data. If the data isn't well set up, then a different approach would likely be best. As the source files don't need to be opened, the question about the macros disappears.

 

[JakeTheSnake3.0]

This might not help with having to read in all of the file's contents, but it will disable any macros.