Help Please :)

I have a workbook with several Identical worksheets each is for a differant person
I have a command button on the firstt sheet
I want to be able to use the command button to clear the contents of a range of cells ("B5:I24") on each of the eight worksheets.

I have gotten the code to scroll through each work sheet and select the range but when I put in the line for clearing the range of cells it works fine for the first two worksheets then errors out "Subscript out of Range"

How do I get around this error?

I appreciate any help someone can offer

Thanks
Charles

Hello Charles,

Use a loop to step through the worksheets.

Macro Code:

Public Sub ClearData()

Dim Wks As Worksheet
Dim I As Integer

Application.ScreenUpdating = False
For I = 1 To Worksheets.Count
Set Wks = Worksheets(I).Activate
Wks.Range("B5:I24").ClearContents
Next I
Application.ScreenUpdating = True

End Sub

Sincerely,
Leith Ross

That looks like it will work but I forgot to mention that there are other worksheets in this workbook that I don't want to clear.
It looks like the code would select all worksheets and clear the contents of the specified range.

I need to select only the 8 worksheets that I want to clear.
Can you show me how to modify the loop to do this ?

The sheet names That I want to clear are
C01 A RR-RS
C01 B RR-RS
C01 C RR-RS
C01 D RR-RS

C03 A RR-RS
C03 B RR-RS
C03 C RR-RS
C03 D RR-RS

There are 10 more sheets I don't want to clear
8 of the final 10 sheets only contain Charts

Thanks for the help.
Charles

Public Sub ClearData()

Dim WksList(8) As String
Dim Wks As Worksheet
Dim I As Integer

'Load Worksheet List
WksList(1) = "C01 A RR-RS"
WksList(2) = "C01 B RR-RS"
WksList(3) = "C01 C RR-RS"
WksList(4) = "C01 D RR-RS"
WksList(5) = "C03 A RR-RS"
WksList(6) = "C03 B RR-RS"
WksList(7) = "C03 C RR-RS"
WksList(8) = "C03 D RR-RS"

Application.ScreenUpdating = False

For I = 1 To UBound(WksList)
Set Wks = Worksheets(WksList(I)).Activate ( ERRORS OUT ON THIS LINE) Subscript out of range
Wks.Range("B5:I24").ClearContents
Next I

Application.ScreenUpdating = True

End Sub

I put this code under a command button click event will that work?
Or should it be somewhere else?

Thanks for your help I really appreciate it.

Charles

Hi Charles,

The subscript out of range error indicates that a sheet name is misspelled or a sheet is missing. You can call the macro from the command button click event.

Calling Example:

Private Sub CommandButton1_Click()
Call ClearData
End Sub