Macro to consolidate multiple worksheets into Master based on cell data?

[GarnesGambit]

Hi all,

I'm hoping I can get some advice as I'm not entirely sure where to start...

I have a spreadsheet which is a master file of data for multiple "stores" (Store 1, Store 2, Store 3 etc) I use this master to track what stores have particular merchandising "plans" (some stores have the same plans, others are unique).

Each "plan" in this master is hyperlinked into 2 ways:
1 - a PDF of the visual plan
2 - an Excel workbook with listed contents for the plan

I basically have 2 folders, one where I save all PDFs and two where I save all Workbooks

The PDFs/ Excel hyperlinks are great when you want to pull up an induvial piece of information, especially as some plans belong to multiple stores (see column L in my attached image), but I'm looking for a way to consolidate multiple of these workbooks, based on the store name, into 1 master sheet? So I'm left with a list of all the plans associated with that store (all workbooks have the same headers and columns)

You can see from the image I have attached:
Column H - the hyperlinks to plan pdfs
Column I - the hyperlinks to listed contents in Excel workbooks
Column L - this is a formulated list of stores created by whether there is a 1 in the right hand columns (P to AJ+)

Is there a way for me to filter on a particular store, then have a macro button that would somehow consolidate the individual workbook plans for that store into a Master file? Perhaps in a way that I can highlight I6 down the to last row, then click a button and it consolidates those file names (because these are what the files are called) into 1 Master list? I

All of the "plans" are saved in the same place H:\Example\Planogram\All Plans - Excel\Example Plan Name.xlsm (this is far easier than having a separate file for each store as, like I said above, some stores have the same plans, and also sometimes stores will change their plan.

Thanks so much in advance for any help that comes my way! I've given myself a headache Googling this and trying to figure it out!

All the best
Lauren

 

[bobaol]

how about have the store name in column A? so that A1 to A999 contain one store name, then the next store in A1001 to a1999, and so forth. this way, you are only going up-down with your store and the plan. it is redundant, but it is easier to manipulate when the data is all lined up.