Help Please :)

[mrdata]

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

[Leith Ross]

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

[mrdata]

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

[Leith Ross]

Hello Mrdata,

Here is the revised macro for the sheets of interest.


Macro Code:

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
Wks.Range("B5:I24").ClearContents
Next I

Application.ScreenUpdating = True

End Sub

Sincerely,
Leith Ross

[mrdata]

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

[Leith Ross]

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

[mrdata]

I checked the sheet names and some were miss spelled sure enough fixed all that still errors out on the same line Subscript out of range.

Thanks for contimuing to help me iron this out

Charles

[Leith Ross]

Hi Charles,

Could you send me a copy of your workbook? You can e-mail me at [email protected].

Thanks,
Leith Ross

[mrdata]

I sent you the file

Thanks
Charles