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:
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.
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.
