Clear Contents Macro

[MSE]

I have a workbook with six worksheets. On Sheet 6, I want to create a macro
linked to an Excel button from the forms toolbar. I want the macro to only
clear the contents of Sheet 1, Sheet 2, Sheet 3, Sheet 4, and Sheet 5 in the
following cells C7, C9, C16:C23, and C26:C33. I am trying to write a code
that will do this, any thoughts?

 

[Don Guillett]

try this.

Sub clearcellsonsheets()
For i = 1 To 5
ms = "sheet" & i
Sheets(ms).Range("C7,C9,c16:C23,C26:C33").ClearContents
Next i
End Sub

 

[MSE]

Thank you for your input. When I try to run the Macro the screen switches
to visual basic and I get a message that says Run-time error '9' Subscript
out of range, Continue, End, Debug, Help. Any thoughts?

 

[Don Guillett]

Number of sheets? Name of sheets?
If desired, send your workbook with these snippets and the code to my
address below

 

[MSE]

I have six sheets. Their names are Sheet 1, Sheet 2, Sheet 3, Sheet 4, Sheet
5, and Sheet 6. This part of the code is having the problem I think.

Sheets(ms).Range("C7,C9,C16:C23,C26:C33").ClearContents

It gets highlighted in yellow with a yellow arrow pointing to it when I try
to run the macro. Any ideas?

 

[Don Guillett]

Send your workbook to my address below and I'll have a look

 

[Gord Dibben]

Try changing "sheet" to "Sheet"


Gord Dibben MS Excel MVP

 

[Dave Peterson]

If your sheet names really have that space character, then change:

ms = "sheet" & i
to
ms = "sheet " & i

 

[Don Guillett]

Sub clearcellsonsheets()
For i = 1 To Worksheets.Count
If Sheets(i).Name <> "Department Total" Then
Sheets(i).Range("C7,C9,c16:C23,C26:C33").ClearContents
End If
Next i
End Sub

Didn't work so I changed to ABOVE & suggested other changes to wb
'Sub clearcellsonsheetsold()
'For i = 1 To 5
'ms = "Sheet " & i
'Sheets(ms).Range("C7,C9,c16:C23,C26:C33").ClearContents
'Next i
'End Sub