VBA: Use of dropdown menu in combination with Breaklink


New Member
Dec 24, 2013
Hi all,

Each month, I have to make the same sales report for all European countries.
I basically have one cell that contains a dropdown menu with all countries.
The sales & plan numbers of the sheet change according to which country I pick from the dropdown menu.
I than manually Copy the sheet & break all links & save it with the name of that country.

As this is all "monkey work" and I want to improve my processes to have more time on my hands for other priorities, I wanted to build a macro to do this for me.
So, I basically want to make a macro that does the following;
1) Picks the first value in the dropdown menu.
2) Copy this sheet to a new workbook.
3) Break all links
4) Save this file as e.g. "JAN SALES COUNTRY NAME". The country name of course depends upon the selection in the dropdown menu.
5) repeat the above steps for all countries.

I've managed to make one that makes separate files for all countries, but I can't figure out on how to breaklink automatically and save according the country specified.

Do you guys know which code I can use to do this?

Thanks a zillion & Merry Christmas.

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Watch MrExcel Video

Forum statistics

Latest member
Mark Rob

This Week's Hot Topics