Run macro depending on selection in cells

[dado6612]

Hello, I've been googling this and just can't make it work for me. Cells A1,A2 and A3 have a dropdown list, datavalidation one, and depending on whats selected from any list to run corresponding macros
so ex if A1=option1 call macro1
if A1=option2 call macro2
if a2=option1 call macro3
if A2=option2 call macro4
etc
This is what's not working for me atm

Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo fixit
 Application.EnableEvents = False
 Select Case Target.Address
 Case "b2"
 If Target.Value = "Add" Then
                Call Macro1
    Else: Call Macro2
End If
Case "h2"
 If Target.Value = "Add" Then
                Call Macro3
        Else: Call Macro4
    End If
    Case "n2"
 If Target.Value = "Add" Then
                Call Macro5
            Else: Call Macro6
    End If
    End Select
fixit:     Application.EnableEvents = True
Application.CutCopyMode = False
End Sub

Thank you

 

[mumps]

I'm a little confused. In your description you say

Cells A1, A2 and A3 have a dropdown list

but in your code you refer to B2, H2 and N2. Please clarify where your drop downs are actually located.

 

[dado6612]

As in code, sorry. Was trying to explain what I need by the example

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B2,H2,N2")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    On Error GoTo fixit
    Select Case Target.Column
        Case Is = 2
            If Target.Value = "Add" Then
                Call Macro1
            Else
                Call Macro2
            End If
        Case Is = 8
            If Target.Value = "Add" Then
                Call Macro3
            Else
                Call Macro4
            End If
        Case Is = 14
            If Target.Value = "Add" Then
                Call Macro5
            Else
                Call Macro6
            End If
    End Select
fixit:
    Application.EnableEvents = True
End Sub

 

[dado6612]

This works perfectly, but now, no idea how, them macros aren't

Sub Macro1()'
' Macro1 Macro
Range("C5:C55").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("B3").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet1").Select
    Range("a1").Select
End Sub

But instead of Adding values it subtracts them. Any idea?
I did the macro by macro recording and doing it manually, it worked normally.

 

[mumps]

Try:

Sub Macro1()
    Sheets("Sheet1").Range("C5:C55").Copy
    Sheets("Sheet2").Range("B3").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

 

[dado6612]

It still keeps subtracting them instead of adding them together (need them 2 ranges being summed, as paste special add does). Confused. Have no other codes in workbook.

 

[mumps]

PasteSpecial Paste:=xlPasteValues

does not calculate the sum. It simply pastes the values that already exist in the range. You need to use the "Sum" worksheetfunction to add the values in the range:

Sub Macro1()
    Sheets("Sheet2").Range("B3") = WorksheetFunction.Sum(Sheets("Sheet1").Range("C5:C55"))
End Sub

 

[dado6612]

Same problem, keeps subtracting. Let's forget about this, will try to find some other way, maybe bug in the excel

Thanks for solving the initial problem