Automate Action of Selection in Inter-Dependent Dropdowns via Macro & Moving Data.!

Automate an Action of Selection in Inter-Dependent Dropdowns via Macro & Moving Data.!

I tried my best to have and Expressive Title to explain the requirement.

Dear Forum,

I am really grateful to everyone on the forum who has taken time and efforts to help me directly or even indirectly by suggesting useful pages of information..

Thanks a lot really..

In this query, I require help to Automate a series of steps taken to make a report..
I dont have a Dummy Data WorkBook ready as yet as the Actual one contains lot of confidential data but I can definitely post one on confirmation if this is actually possible.

I am working on Insurance Data and need to make a few Reports for Clients and this process is redundant and time-consuming as the pattern of reports for each Insured Family remains the same..So I wonder if we could Automate these steps with a Macro..

I have the Insurance Data of Clients and their Families data together.
This data is Viewed Differently on the basis of several options such as the Payment Mode and the Individuals Data seperated from the Family Data.

Individual Data is a Subset from the Family Data..

Like:
Mr A Brown, Mrs A Brown and Mr Lil Brown are all Family Members of the Brown Family and we can view their data all at once and seperately by selecting individually using the principles of MultiLookup.

Payment Mode - YLY,HLY,QLY,ECS, SGL i.e. Yearly, Half_yearly, Quarterly, Monthly, Single.

There are 2 Sheets I am working on , The Data Sheet which contains all the data of the clients Insurance information and also the policy information.

The other Sheets is the Viewable portion which has 3 Interdependent Dropdowns.
1. The Family Dropdown to select one element ( 1 Family's Data) from Several Families in the Main Data sheet.

2. Individual Family Member - ( 1 Family member's name from the Selected Family in the first dropdown) the Family Members Dropdowns list is dependent on the Family Dropdown.

3. Options of Investment = There are 3 types of Simple Interest, Compound Interest and Recurring Investment.

When making reports, First what is done is, Selecting the Family then Selecting the Different Options of Investment so Each Investment Option would be 1 Report so there would be 3 Reports fixed now evertime the DropDown of Option is changed the Display of data of changes now I want to store this data into one Sheet as Values with the same Format in another WorkBook..

The Name of the WorkBook would be The FamilyName + " Planning"
1st Sheet Name: The FamilyName + Simple Interest ( i.e Option Selected)
2nd Sheet Name The FamilyName + Compound Interest ( i.e Option Selected)
3rd Sheet Name The FamilyName + Recurring Investment ( i.e Option Selected)

After the Combined Investment for the Family, the Family members dropdown would be selected and then again for each member there would be three options of Investment mentioned above. i.e. Simple Interest, Compound Interest and Recurring Investment.

This process will continue till all the reports for each Family member is made.

So, if we are considering the Family of Mr A Brown, Mrs A Brown and Mr Lil Brown then we would 3 Reports for the ENtire Family and then again 3 Reports for each Member Individually so a Total of 12 Reports..

The entire sheet is formula based and populates reports based on the selection in the different dropdowns which are also interdependent.
This is done manually but it would be great if this could be Automated with a Macro..

Basically, the major portion is to action a Selection of all the elements in the dropdown and then creating a New WorkBook Dynamically and then Adding Sheets one after the other in this WorkBook.

Warm Regards
e4excel