Run macro when drop-down value changes

[Bob L]

Hi all,

I tried a couple things to no avail and every solution I could find online was for the same tab (Worksheet_change).

Currently, I have an excel file with a tab called Pivot that has a drop-down menu in cell E7 and a tab called Data that has this macro in it:

Sub Macro_Mar()

Range("AA3:AL150000").Select
Selection.ClearContents
Range("AA2:AL2").Select
Selection.Copy
Range("AA3:AL150000").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub

It's a simple macro that deletes old data and copy/paste value the new one as the file is too slow if I keep all the formulas. Only the first row (row 2) has the formulas in it full time which changes with the drop-down menu change.

Right now, every time I change the drop-down menu, I go click on a button in the Data tab to start the macro like a peasant, but I was wondering how can the code above run every time the drop-down value changes automatically?

Thanks.

 

[Joe4]

OK, do the following three things:

1. Make the following changes to your current Macro (I cleaned up your code a little, and added a sheet reference),

Sub Macro_Mar()

Sheets("Data").Activate
Range("AA3:AL150000").ClearContents
Range("AA2:AL2").Copy
Range("AA3:AL150000").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("AA3:AL150000").Value = Range("AA3:AL150000").Value

End Sub

2. Move this Macro out of the "Data" sheet, and insert it into a new, General module in your workbook

3. Add this new VBA code to the "Pivot" sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$E$7" Then
        Call Macro_Mar
    End If
End Sub

Now whenever you make a change to cell E7 in the drop-down on your Pivot sheet, it should run your Macro against the Data sheet.

 

[Bob L]

OK, do the following three things:

1. Make the following changes to your current Macro (I cleaned up your code a little, and added a sheet reference),

Sub Macro_Mar()

Sheets("Data").Activate
Range("AA3:AL150000").ClearContents
Range("AA2:AL2").Copy
Range("AA3:AL150000").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("AA3:AL150000").Value = Range("AA3:AL150000").Value

End Sub

2. Move this Macro out of the "Data" sheet, and insert it into a new, General module in your workbook

3. Add this new VBA code to the "Pivot" sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$E$7" Then
        Call Macro_Mar
    End If
End Sub

Now whenever you make a change to cell E7 in the drop-down on your Pivot sheet, it should run your Macro against the Data sheet.

Huh, interesting, I tried something similar and it didn't work, guess I messed up somewhere. Yours works perfectly. Thanks!!

 

[Joe4]

You are welcome!