How to efficiently have a summary sheet that reads from multiple sheets

[Excel_lent101]

Hi everyone - I was recommended to look for VBA ideas in this forum.

The situation:
I have multiple sheets that contain data of fixed income assets. These sheets have some data that are input manually (amounts, cost, etc.) and some read from bloomberg ( price, yield, etc), and I have a summary sheet that has all the data but summarized.

So, my question is I want to build an efficient VBA module that doesn’t require a human interference except for downloading raw data of assets and market from its sources.

I would like to see your ideas on that. Note that I’m a first timer at VBA.

Thank you in advance.

 

[Joe4]

Welcome to the Board!

What you are describing (many related data lists) is really a relational database. As such, as relational database program, such as Microsoft Access or SQL will handle this sort of thing much better than Excel can, as that is what they were designed for. Excel was not designed for that purpose, though you can "brute-force" Excel to behave that way (though it tends to be a bit clunky, slow, and cumbersome).

If you don't have the resources to use (or learn) Microsoft Access and absolutely have to use Excel, I would look at using Power Query, which is a tool you can use inside Excel that allows you to perform some database-type operations in Excel. There is a forum for Power Query here on this board where you can ask questions (and also has some good information in the Stickies at the top of that forum): Power Tools