Grabbing data listed vertically under headings

Hi All,
Im looking for some ideas on how to grab data from a source where everying is listed in 1 column, under various headings. See image attached

The Headings never change (small cap, large cap, equity etc etc). Under each heading sits a list of investments, but the number of investments can change on a daily basis, i need write a macro that will grab all of the investments and past them some place else (into a template for uploading to a website). I cannot use a fixed array because the number of investments change all the time.

Its a challenging one! any ideas would be Hugely appreciated!
Cheers,
Dave

Capture.jpg

Hi

Put up an example file and show exactly how you want the output to be located.

rylo

Hi Rylo,
Thanks for the reply, i have attached a sample. basically i need to capture data from the Orange tabs and place them in the templates which are purple, In reality there are about 20 Purple tabs not just the two in this sample. Ive put some crude colour coding to show the data in orange tabs which is repasted into the Purple ones..
Im trying to avoid having to copy and paste the data, i cant link the cells because the number of investments listed changes everyday..
Is it doable?
many many thanks for having a look!

Portfolio Listings sample file.xlsm

Hi

How do you determine which of the purple tabs gets the data? So why does the crossholdings data from AEIB go to the crossholdings column in Balanced, and not Conservative (or one of the other purple tabs)?

rylo

Its because of various interfunding structures, For example there might be 5 purple tabs which use AEBT for domestic equity for example but AEIT for intrest bearing securities. They are not one for one. There are only the 3 source (orange) reports, but they feed upward of 20 purple tabs. it gets pretty complicated. In the example you might notice that australian shares in 'balanced' come from AEBT but in 'conservative' come from large cap (listed in the All others tab). I thought about trying to build some sort of matrix for reference but would have alot of variables.
Generally speaking International Equity always comes from the same spot, Cash is always just Cash. But something like 'interest bearing' could come from one of two orange sources AEBT or AEIT, dependant on which purple tab needs to be filled. So i guess i need a macro that will build every purple tab i need, or a macro that will reference the orange tabs and fill in the purples, allowing for the changing amount of securities!
Im thinking if i could re-format the orange tabs into one sheet, breaking out the investment types horizontally, then i could just use cell = links allowing for up to 1000 lines or something...

you will need to determine the set of rules that govern what goes where. They must exist, as you would have used them to put the data in the right place. If you can detail them (somehow - matrix, if type statements etc) then there is probably a good chance that we could build a macro to do the manipulation. But you must have the rules for us to work.

rylo