VBA Q(s), a composite task mainly regarding External workbooks

Cats and Dogs

New Member
Aug 19, 2015

​Hi all,
First post. I do hope that I'm complying with the norms and terms and that my question is clear and not overly complicated.

That out of the way... I'm clearly a beginner, using office 2010, tasked with (and so far failing miserably at) putting together some code for my new position. I need help in achieving the following:

I have 3 external workbooks ExtWB-Anne.xlsx, ExtWB-Bonnie.xlsx, ExtWB-Chloe.xlsx and a master workbook MstrWB.xlsm in the folder (C:\Path).
In the
ExtWB-A.xlsx, ExtWB-B.xlsx and ExtWB-C.xlsx corresponding to workers Ann, Bonnie and Chloe- I have payroll protected sheets divided by months: August 2015 and onwards.
I'm trying to pull the data into the master wb from the external wbs, according to both month and whether that employee is registered as working that month.

If I have it correctly I need to:
0. Trigger action on Open or Sheet change of MstrWB.xlsm.
1. Scan the folder in which the external workbooks are present.
2. Extract data only from the relevant files for those employees that have worked in the period of time as specified in MstrWB.xlsm, so that if for example Bonnie is missing from October's roster, that data will be left out (I was thinking that perhaps using an INDIRECT reference that pulls the roster from the relevant monthly worksheet in MstrWB.xlsm)
2a. Copy the the relevant monthly sheet from that external workbook corresponding to the correct month on MstrWB.xlsm (again, perhaps using an INDIRECT reference).
2b."Unprotect" each
protected workbook (password:"password").
3. Paste data in MstrWB.xlsm.
4. Close the external workbooks without saving changes, and
4a. Re-apply external workbooks protection.
5. Put necessary ErrorHandlers.

Your help is much appreciated!

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.


Well-known Member
Oct 17, 2005
Office Version
Hey Cats & Dogs (funky name by the way),
you question is clear and not overly complicated, but you forgot to post the code that you created so far :). If you've got no code yet, try e.g. these pages to understand some of the basics:
Excel VBA Tutorial - EasyExcelVBA.com or VBA Course : Introduction
I'd be happy to help you with chuncks of code once you get yourself started. As you might have noticed you can't attach files here, but a link to dropbox/google drive files will do the trick if you want to share a file.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics