Creating a macro to change values in a drop down list and export the resulting output

[Tommy Pickles]

Hi,

I've been searching the web and so far had no joy in finding a solution to what I need, I'm hoping someone can help. I am pretty basic with macros having only used them a handful of times at a very simple level.

I have built a report that will provide users with a data output based on the selection given in 3 drop down lists (created using data validation). One is the month (Jan-Dec), the second is a region (North, South, East, West) and the third is a Customer name (Customer A, Customer B, Customer C etc etc).

The date only has a bearing on the output data shown in the report, however the region drop down list is tied to the customer list. So, if a user selects East, this feeds a vlookup on another sheet which in turn populates the third drop down list with the Customer names within that region.

I have created the following macro which will allow a user to take a hard copy of one customers data once they have made all of the necessary selections on the drop down menus (removes all lookups, other names, financial data etc). This hard copy can then be shared with the relevant customer without the worry that they could use the data hidden behind the report.

The code for this function is as follows:
Sub ExportData()
'
' ExportData Macro
'
'
Sheets("MB Summary").Select
Sheets("MB Summary").Copy
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("W16").Select
ActiveWindow.SmallScroll Down:=6
Rows("26:40").Select
ActiveWindow.SmallScroll Down:=-3
Rows("17:40").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("D16:P16").Select
Selection.ClearContents
Range("V22").Select
ActiveSheet.Shapes.Range(Array("Button 1")).Select
Columns("V:X").Select
Selection.Delete Shift:=xlToLeft
Range("Y20").Select
ActiveSheet.Shapes.Range(Array("Button 1")).Select
Selection.Delete
Range("A1:S59").Select
ActiveWindow.SmallScroll Down:=-39
ActiveSheet.PageSetup.PrintArea = "$A$1:$S$59"
Range("W23").Select
End Sub


As I produce this report for people who represent a region (and therefore a number of customers) I want to add a macro which will carry out this function, but for every customer in the region so that it becomes less time consuming for the report users to generate multiple reports for distribution.

The lookups in the sheet are controlled by the drop down menu though so my goal is that a user could select the region, click a button, and the macro would take the list of names and carry out the above function for each name on the list.

Could anyone assist please?

Thanks