Hello Natman,
The following codes will do the task for you:-
VBA Code:
Option Explicit
Sub Test()
Dim ws As Worksheet, wsM As Worksheet
Dim sVal As String
Set wsM = Sheets("Master")
Set ws = Sheets(ActiveCell.Value)
sVal = ActiveCell.Offset(, 5).Value
Application.ScreenUpdating = False
wsM.[B12].CurrentRegion.Offset(1).Clear
With ws.[A13].CurrentRegion
.AutoFilter 1, sVal
.Offset(1).Copy wsM.Range("B" & Rows.Count).End(3)(2)
.AutoFilter
End With
Application.ScreenUpdating = True
End Sub
and
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("M2:M7,X2:X7")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Test
End Sub
As I mentioned in an earlier post, merged cells create major problems for VBA codes and with the plethora of merged cells in the Master sheet, the only way I could get this to work was to un-merge the cells in the Master sheet.
I have attached your sample with the above codes implemented and showing the "un-merged" status of the Master sheet. You'll note that the codes do the task but your columns may no longer align as you had them previously nor would the totals be in their correct columns. You will need to re-format the Master sheet without using merged cells.
With the above codes, the "Test" sub needs to be placed in a standard module and the Worksheet_Change event code needs to be placed into the Master sheet module. For the worksheet_Change event code, do this:-
- Right click on the Master sheet tab.
- Select "View Code" from the menu that appears.
- In the big white code field that then appears, paste the above Worksheet_Change event code.
Each time you make a selection from the source sheet name drop downs (M2:M7 and X2:X7), the relevant data will be immediately transferred to the Master sheet from the selected source sheet. No button is required.
Your sample worksheet is
here.
Cheerio,
vcoolio.
P.S. If you do not want the Master sheet to clear with every data transfer and instead prefer to use your "Clear Sheet" button then remove this line from the code:-
VBA Code:
wsM.[B12].CurrentRegion.Offset(1).Clear
Alternatively, just place an apostrophe(') in front of the line of code allowing you to keep the line of code but simply de-activating it.