Call Macro on a specific sheet / Excel

Question

I want to run a macro on a specific sheet, in my case the sheet is called "Tablet".

If a cell value in "Tabelle1" changes, I want to run this macro in the "Tablet" sheet.

Code in my Tabelle1:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$2" Then
        Call Delete_OptionB1
    End If
End Sub

This part works.

Macro Code:

Sub Delete_OptionB1()
'
' Delete_OptionB1 Makro
'
     With Worksheets("Tablet")
    .Range("M2:AD2").Select
     Selection.ClearContents
    End With
End Sub

This wont do the job. Any suggestions how I get this working?

Answer 1

In your code using a with block

 With Worksheets("Tablet")
.Range("M2:AD2").Select
 Selection.ClearContents
End With

You are selecting .Range("M2:AD2").Select but then clearing the contents of the selection on whatever sheet may be active when you Delete_OptionB1. Change to include a . - .Selection.ClearContents.

Even better, get rid or the With...End With and Select altogether. A single line will do it all:

Sub Delete_OptionB2()
'
' Delete_OptionB1 Makro
'
    Worksheets("Tablet").Range("M2:AD2").ClearContents
End Sub