VBA: copy data from multiple workbooks

DesAjax

New Member
Joined
Sep 19, 2014
Messages
2
Hello,

I just haven't got the knowledge myself to come up with a code that works. I tried adjusting some codes I found on the internet, but I just can't achieve what I want with my basic knowledge of vba-coding. I hope someone can help me with this. I'm trying to come up with a vba-code that does the following.

I have a Workbook (it is called "Results.xlsm" and it is located at "C:\MyDocuments\") in which I want to collect (read: copy) data from specific cells of other workbooks. These other workbooks are all located within different folders of the same directory "C:\MyDocuments\". All those workbooks have one thing in common and that is that their description always contains the word "Test". All these workbooks are protected with a password. The password is "TestTest".

The vba-code has to search in all the (sub)folders of the directory "C:\MyDocuments\" and has to find all the workbooks that have the word "Test" in their description. Then, one by one, the vba-code has to:
  • open the workbook;
  • unlock the workbook with the password;
  • copy the data of cell "A1" from the sheet called "Sheet1";
  • paste this data into cell "A1" from the sheet "Results" in the workbook "Results.xlsm";
  • paste the full name of the workbook where the data has been copied from in cell "B1" from the sheet "Results" in the workbook "Results.xlsm"
  • lock the workbook again with the same password; and
  • close that same workbook (without saving any changes).

The vba-code will then open the second workbook that meets the criteria, repeats the same steps and will copy the data in the workbook "Results.xlsm" in the cells below the ones that already contain data (e.g. A2 and B2, when data is copied from the second workbook that meets the criteria). This will continue, one by one, until the data in cell A1 from every workbook with the word "Test" has been copied to the workbook "Results.xlsm". So if 100 workbooks meet the criteria, than the range A1:B100 will be filled with data in the workbook "Results.xlsm".

Is there someone that can help me with this? Is this even possible to achieve?

Thanks in advance.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,651
Messages
5,838,584
Members
430,557
Latest member
MK15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top