Excel VBA- Creation of a New datablock with criteria

Question

I'm a freshman using VBA. I'm seeking for a macro that will generate a new sheet with only the information for a specific strategic health authority that the user selects from a drop-down menu. A brand-new sheet should be used to build the new data block.

The chosen health authority's name should be the title of the new sheet. The various health authorities are as follows:

 The macro should run off a button, located on the User sheet.

I need it to run in a reasonable amount of time (<1 minute).

I already have the developer tab installed, and I know how to make a button. I'm not sure how to make the drop-down box, but. I require Excel for Mac Version 16.68 to work with Excel 2021 on a PC, but I'm currently using Excel for Mac Version 16.68.

Answer 1

To insert the dropdown, you can go to developer tab -> Insert -> Form controls Combo Box. Then you can reference it in VBA using Sheets("Sheet1").DropDowns(1)