Macro - Collect data - Select folder and loop through all xls files

Hello kostas,

Here is the revised macro to copy the data from the workbooks you select onto the two worksheets in the main workbook. It uses the Open file dialog to allow you to choose the directory and files. Only workbook files (.xls) are listed.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Hi
try the macro codes in the text file attached. Change file path if necessary It will do the rest for you
ravi

Thank you both very much for responding so quickly. I will try them out and report back here with how they work.

Again thank you,

Kostas

Ok,

I have some feedback,

Leith,

I run the macro, selected a file and then it broke says "subscript out of range" at

Please Login or Register  to view this content.

I believe it has something to do with the way it locates the row/column but I don't clearly understand what goes wrong.

Ravishankar,

I run your macro but it breaks at many different points. The issue is that I have to rename the sheet names (I think) with the ones I use and I'm not sure which worksheet is the one opened and which worksheet is the one the data goes to in the code you have given me, so I can replace them correctly.

sorry for not understanding much but im quite an amateur in VBA.

Any further assistance will be greatly appreciated.

Regards,

Kostas

Hello Kostas,

Referencing can get a little tricky when using multiple workbooks. I fixed the code and ran a few tests. The results were correct. Give a try on your end. Here is the revised code.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

HI
I have used Sheet1 for both source and destination file. If they are different let me know.
Ravi

Leith,

Your solution worked amazingly. It's just excellent. Thank you very-very much for all the time you put into this.

The only amendments I had to make to the code were the following:

I added a

Please Login or Register  to view this content.

right after the

Please Login or Register  to view this content.

to avoid being asked about updating external links etc from the macro.

I also added a

Please Login or Register  to view this content.

at the end of the macro after the

Please Login or Register  to view this content.

to avoid being asked whether I want to save the file or not when it's being closed (since it is opened as read only it asks for save/save as in the end).

Finally I had to make a small correction on

Please Login or Register  to view this content.

, instead of CG it had to be CB as it was rightly coded above (otherwise it was copying the wrong column from the source file).

Overall excellent and thank you once again.


Ravinshankar,

The source workbook has a sheet called "data" and I want to copy the columns BG and CB.
Column BG goes to the destination workbook's sheet called "formdata" and column CB goes to the destination workbook's sheet called "formdata2".

I can't ask you to spend more time on this request as Leith's answer succesfully solved it. However if you are interested academically to provide a leaner and quicker solution I'll be happy to receive it!

Many thanks for your efforts and time everyone. I will update the initial post to include the final solution in the bottom so every can see it directly.

Regards,

Kostas