CODEWITHSUNDEEP
excelvba
user10104344
user10104344

Reputation:

Calling macro based on cell value

I am new to excel macros so I need help. I want macro to be triggered based on specific value. I used CONCATENATE function to get the value which is first and last name and month e.g. "Barna Gabor July" and I want macro to be executed based on the contents. Until now I have this:

Sub worksheet_Search(ByVal Target As Range)
Set Target = Sheets("Master File").Range("I12")
If Target.Value = "Barna Gabor July" Then
Call Barna_July
End If
If Target.Value = "Barna Gabor August" Then
    Call Barna_August
End If
If Target.Value = "Barna Gabor September" Then
    Call Barna_September
End If
If Target.Value = "Barna Gabor October" Then
    Call Barna_October
end if

*etc.....*

End sub

however I am getting an error "method range of object _worksheet failed" and when I go to debug it it leads me to the macro.

Macros that need execution are the same just sheets and ranges are different as I have more people and tables:

Sub Gabor_July()

    Sheets("Gabor").Range("q7:u15").Copy Destination:=Sheets("Master File").Range("c6:G14")
    Application.CutCopyMode = False

End Sub

Can you show me where I am going wrong?

Macro is good if I assign it to a button but not when using it in above command

Thanks

Upvotes: 0

Views: 110

Answers (1)

Xabier
Xabier

Reputation: 7735

I believe the following will do what you expect it to, simply replace your code with the one below, this code should be placed under the Sheet Sheets("Master File"):

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$I$12" Then
        Select Case Target.Value
            Case "Barna Gabor July"
                Barna_July
            Case "Barna Gabor August"
                Barna_August
            Case "Barna Gabor September"
                Barna_September
            Case "Barna Gabor October"
                Barna_October
        End Select
    End If
End Sub

Upvotes: 2

Related Questions