Creating dynamic master sheet based on value and data from other files

Hey folks,

I dare to ask you for advice before I even started my litte project

My goal is to create a master sheet/file which is filled dynamically with data from other excel files.
These files are all build up on the same template (same sheet and columns names and coordinates - different files names though - surprise ). In sheet "Land Rights" of these files there is column A with a string "yes" or "no". If the value of column A is "no", I want the whole row to be added/displayed in the master sheet as long as nobody changes the value to "yes".
Hence the size of the master sheet is variable according to the row having "no" in column A from all files which I have to define.

Since I never had the need to get in touch with VBA and I couldn't think of a solution without... do I need to program a macro in VBA in this case or is there any path to follow with a native functions?

I'm more than thankful for recommendations and tips.

Cheers,
dr. idlebert

Hello dridlebert,


Here's a VBA option:-

Please Login or Register  to view this content.

I've assumed:-

- All source files and the destination file are in the same folder.
- All data in all files (source and destination) starts in Row2 with headings in Row1.
- The destination sheet in the open destination workbook is named "Master".
- The columns of data in each source workbook are A - M. The same in the destination workbook.
- The code will also list the source file names in Column N of the destination workbook. You'll need to add a heading (such as "Source WB") in cell N1 of the destination workbook.

I hope that this helps.

Cheerio,
vcoolio.

Thanks both of you for quick response and providing helpful code!
I decided to go with the VBA option and tried to adjust the code for my specific needs. Unfortunately I was not able to succeed
It's about time to enroll in a VBA course I guess... what do you guys think how long it will take to write such "easy" scripts by myself?

In the meantime I'd kindly ask you to adapt the code one more time...

The required adjustments are

• relative path for stgP
• it shouldn't read itself and some other files within the while loop (scan all *.xlsm file except itself and file1.xlsm, file2.xlsm, ...)
• it needs to copy columns A to AD from the source files
• put source workbook name in column AE
• when I run the macro, it should delete all data in Master except row A (headlines). -> More a refresh function than to an add all data to the existing one.

Thanks guys!

Originally Posted by avk

If you attach sample file with required result it will more helpful both.

Here is an example of a source file with some test data.
The destination file is meant to be fill by the macro.
There are approx. 25 source files, all of them .xlsm.
The structure of the "Overview Land rights" is all the same.

Hey vcoolio,

indeed it helped me a lot. But I'm still not capable of integrating some lines to skip a couple of defined file(name)s which are inside the same folder as all the source files with same extension but are not supposed to be read due to a different structure and content.

Yes, it feels wrong to ask you one more time to solve my problem and write code for me. I hope to be able to write it by myself very soon

Have a nice Sunday!

Hello dridlebert,

If you have files in the same folder with a different structure and which need to be left alone, then the simplest thing to do would be to create a new folder for these files and move them into the new folder (named differently of course). Thus, in future, should you need to deal with them as with the other files you could use the same code, referencing the different path, assigned to a second button on the Master worksheet.

Cheerio,
vcoolio.