Clear Contents Macro
 

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?

Clear Contents Macro
 

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?

Clear Contents Macro
 

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?

Clear Contents Macro
 

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?

Clear Contents Macro
 

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?

Clear Contents Macro
 

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?

Clear Contents Macro
 

Try changing "sheet" to "Sheet"


Gord Dibben MS Excel MVP

On Sat, 21 Jun 2008 10:12:00 -0700, MSE wrote:

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?

Clear Contents Macro
 

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

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



MSE wrote:

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?






--

Dave Peterson

Clear Contents Macro
 

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?