Sub clearcellsonsheets()
For i = 1 To Worksheets.Count
If Sheets(i).Name < "Department Total" Then
Sheets(i).Range("C7,C9,c16:C23,C26:C33").ClearCont ents
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").ClearCo ntents
'Next i
'End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Send your workbook to my address below and I'll have a look

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MSE" wrote in message
...
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").ClearCon tents

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



"Don Guillett" wrote:


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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MSE" wrote in message
...
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" wrote:

try this.

Sub clearcellsonsheets()
For i = 1 To 5
ms = "sheet" & i
Sheets(ms).Range("C7,C9,c16:C23,C26:C33").ClearCon tents
Next i
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MSE" wrote in message
...
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?