Collect 1 sheet data from multiple Excel files in C:Folder and them paste just the values

Hi there,

I need to be able to collect the same sheet from multiple workbooks, in sheet1 in a main workbook, but I need to collect just the values, so it won't paste the formulas, I have been working on it for several hours, but I'm not finding a full code that could help me out with that.

Thanks,

Assuming that the multiple workbooks are in the sam folder you could perhaps use a macro like this.

Please Login or Register  to view this content.

You will need to change the string "E:\a_tester\" to the drive and the folder where your files are. This macro will find all ".xlsx" files. If you do have a mix of ".xlsx" and ".xlsm" files you could use a wildcard for the sFile string i.e. ".xls?"

Alf

Thank you so much, it works, the only thing is that it paste the data in A2 instead of A1 but thanks

Thank you so much, it works, the only thing is that it paste the data in A2 instead of A1 but thanks

That is the effect of the offset command to ensure that no data set is overlapping the previous one. To fix that the easiest way is just to add a line after the "loop line" i.e

Please Login or Register  to view this content.

Thanks for feedback and rep

Alf

Thank you, jajaja Is there a way to give you extra reputation? jajajaja, hey now there is something that pops up it says "This workbook contains links to one or more external sources that could be unsafe", it ask me to update or don't I have to click in every file, is there a way to do it with the same vba code?

What I suggested above don't seems to work, instead replace line

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

and see if this works for you. And as I said before you must change the string "E:\a_tester\" with the drive and the folder where your files are stored.

Alf