clearcells based on value in cell on another sheet

[CarDyer]

Hi
Struggling with this and cant find anything on google.

I have a few sheets in a workbook for a game we play. One sheet per person and a TOTALS sheet. Values get added to each person sheet during the game in a specific range of cells, e.g. D3-D7 and G6-G11.

At the end of the game I want to issue a command to clear all the values from the person sheets for the ranges specified. I am guessing that the best way to do this is to have a cell in the TOTALS sheet in which I can enter a 'Y', but open to other suggestions.

I have found solutions to include a cell on each of the sheets but not one that covers updating all sheets in the one go.

Any help greatly appreciated as emptying the cells manually really impinges on our gambling time

 

[Fazza]

hello, maybe something like this? cheers

'one approach
Const sRANGES_TO_CLEAR As String = "D3:D7,G6:G11"
Dim wksLoop As Excel.Worksheet

For Each wksLoop In Worksheets
    Select Case wksLoop.Name
        Case "TOTALS"
            'do nothing
        Case Else
            wksLoop.Range(sRANGES_TO_CLEAR).ClearContents
    End Select
Next wksLoop
Set wksLoop = Nothing

 

[CarDyer]

hello, maybe something like this? cheers

'one approach
Const sRANGES_TO_CLEAR As String = "D3:D7,G6:G11"
Dim wksLoop As Excel.Worksheet

For Each wksLoop In Worksheets
    Select Case wksLoop.Name
        Case "TOTALS"
            'do nothing
        Case Else
            wksLoop.Range(sRANGES_TO_CLEAR).ClearContents
    End Select
Next wksLoop
Set wksLoop = Nothing

Thank you Fazza. Unfortunately that is not working for me. I am getting a runtime error 1004 Intersect of object Global failed.

I lifted your code and put it in a sub and tried to run.

I cannot see where I would enter the 'Y' in a cell in the TOTALS sheet to do the clear.

Also the sheets are named. Does that make a difference?
Thanks

 

[Fazza]

You're welcome.
I don't know why there is the error.
I also cannot see where you'd enter the "Y".
Sheets named is unavoidable - no difference.
cheers