Macro that currently opens 23 workbooks, looking to speed it up

[ks100]

I have a macro right now that does the following about 23 times:


1. Open a workbook
2. Paste that workbook into a sheet called "work area" on my main workbook
3. Do Until on a large range of values
4. Populate another worksheet within the same book (there is one worksheet for each of the 23 books opened)
5. Close without saving the workbook I opened in step 1
6. Clear the workarea sheet


Right now the macro takes about 5 to 10 minutes or so for 4 books. Needless to say it would take quite a while to do all 23.

I think the lag is within the opening and closing of the books. Would it be faster if I just kept a sheet that pulled data from those books instead of opening them?

[Philb1]

Do you have any code we can see?

[Doc.AElstein]

.....
I think the lag is within the opening and closing of the books. Would it be faster if I just kept a sheet that pulled data from those books instead of opening them?

If you are pulling data from closed Workbooks you will, I think, need to reference one cell at a time, so that could take a while. As Philb1 said we need to see a code and maybe also a file or two to see exactly what you are doing. I have a fairly old system but I would never need anything like 5 minutes to open and close 4 books. Anyway you can see if how long that is taking cant you ?
. Usually things take a long while if you are continually referencing the spreadsheet. Every time you do something on a sheet it is like slamming the brakes on. As much as possible you should try to bring as much data in to a code in one go as possible , work on it “internally” as much as you can and then try to paste out large amounts in one go.

Alan