VBA: copy data from multiple workbooks


New Member
Sep 19, 2014

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.

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...