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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Forum statistics

Threads
1,211,679
Messages
6,103,245
Members
447,849
Latest member
4chase

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
Back
Top