Try the following code:_
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("M1")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub
Dim ws As Worksheet, lr As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set ws = Worksheets("Master")
lr = Sheets(Target.Value).Range("A" & Rows.Count).End(xlUp).Row
Sheets(Target.Value).Range("A2:L" & lr).Copy
ws.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
Application.DisplayAlerts = True
Application.CutCopyMode = False
Application.ScreenUpdating = True
The code assumes:-
- All sheets have headings in row 1 and data starts in row 2.
- Your drop down list is in cell M1.
- When a region is selected from the drop down, you want the whole data set (from A:L) for that region transferred to the Master sheet.
No button is required with this code. It is a Worksheet_Change event which means that, when a region is selected from the drop down, and you click away (or press enter or down arrow), the code executes immediately.
Old data in the Master sheet is cleared every time that the code is executed.
To implement the code:-
- Right click on the Master tab.
- Select "View Code" from the menu that appears.
- In the big white field that then appears, paste the above code.
I hope that this helps.