Macro to Clear Data in Cell D37 listed on all sheets Listed in Array

[howard]

I have written code to clear data in Cell D37 on all sheets Listed in the Array

However when running the macro, the data in D37 is not being cleared

It would be appreciated if someone would kindly amend my code

 Sub Clear_Ref_Advices()

Dim arr, Wks

arr = Array(" advice BR1 GRT", " advice BR1 GRT", " advice BR3 GRT", " advice BR4 GRT" _

, " advice BR5 GRT", " advice BR6 GRT")

For Each Wks In arr

Range("D37").ClearContents



Next Wks

 End Sub

 

[kevin9999]

I'm assuming the double-up on BR1 is a typo, and that there is supposed to be a space before each sheet name?

Sub Clear_D37()
    Dim Wks As Worksheet
    
    For Each Wks In Worksheets(Array(" advice BR1 GRT", " advice BR2 GRT", " advice BR3 GRT", " advice BR4 GRT", _
    " advice BR5 GRT", " advice BR6 GRT"))
        Wks.Range("D37").ClearContents
    Next Wks
    
End Sub

 

[johnnyL]

Your code would be clearing the same cell. You need to specify which sheet the D37 should be cleared on.

 

[howard]

many Thanks for the help Kevin. Code works perfectly

 

[howard]

Thanks for your advise Johnny

 

[kevin9999]

many Thanks for the help Kevin. Code works perfectly

You're welcome Howard, and thanks for the feedback.

 

[My Aswer Is This]

Here is how I would do it:
Modify Sheet names

Sub My_Array_Work()
'Modified 6/13/2022  1:56:12 AM  EDT
Application.ScreenUpdating = False
Dim Del As Variant
Dim i As Long
Dim ans As Long
Del = Array("Alpha", "Charlie", "Delta")
ans = UBound(Del)

For i = 0 To ans
    Sheets(Del(i)).Range("D37").ClearContents
Next
Application.ScreenUpdating = True

End Sub

 

[howard]

Thanks My Answer is this for yo help