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?

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"MSE" <(E-Mail Removed)> wrote in message
news:CB51A454-35EE-44F9-9B32-(E-Mail Removed)...
>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?
>

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").ClearContents
> Next i
> End Sub
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "MSE" <(E-Mail Removed)> wrote in message
> news:CB51A454-35EE-44F9-9B32-(E-Mail Removed)...
> >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?
> >
>
>

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
(E-Mail Removed)
"MSE" <(E-Mail Removed)> wrote in message
news4572E32-3865-4261-96C2-(E-Mail Removed)...
> 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").ClearContents
>> Next i
>> End Sub
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "MSE" <(E-Mail Removed)> wrote in message
>> news:CB51A454-35EE-44F9-9B32-(E-Mail Removed)...
>> >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?
>> >
>>
>>

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" 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
> (E-Mail Removed)
> "MSE" <(E-Mail Removed)> wrote in message
> news4572E32-3865-4261-96C2-(E-Mail Removed)...
> > 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").ClearContents
> >> Next i
> >> End Sub
> >> --
> >> Don Guillett
> >> Microsoft MVP Excel
> >> SalesAid Software
> >> (E-Mail Removed)
> >> "MSE" <(E-Mail Removed)> wrote in message
> >> news:CB51A454-35EE-44F9-9B32-(E-Mail Removed)...
> >> >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?
> >> >
> >>
> >>
>
>

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"MSE" <(E-Mail Removed)> wrote in message
news:31A4A3C7-485D-4FC9-9441-(E-Mail Removed)...
>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" 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
>> (E-Mail Removed)
>> "MSE" <(E-Mail Removed)> wrote in message
>> news4572E32-3865-4261-96C2-(E-Mail Removed)...
>> > 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").ClearContents
>> >> Next i
>> >> End Sub
>> >> --
>> >> Don Guillett
>> >> Microsoft MVP Excel
>> >> SalesAid Software
>> >> (E-Mail Removed)
>> >> "MSE" <(E-Mail Removed)> wrote in message
>> >> news:CB51A454-35EE-44F9-9B32-(E-Mail Removed)...
>> >> >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?
>> >> >
>> >>
>> >>
>>
>>

Try changing "sheet" to "Sheet"


Gord Dibben MS Excel MVP

On Sat, 21 Jun 2008 10:12:00 -0700, MSE <(E-Mail Removed)> 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").ClearContents
>> Next i
>> End Sub
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "MSE" <(E-Mail Removed)> wrote in message
>> news:CB51A454-35EE-44F9-9B32-(E-Mail Removed)...
>> >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?
>> >
>>
>>

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").ClearContents
>
> 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
> > (E-Mail Removed)
> > "MSE" <(E-Mail Removed)> wrote in message
> > news4572E32-3865-4261-96C2-(E-Mail Removed)...
> > > 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").ClearContents
> > >> Next i
> > >> End Sub
> > >> --
> > >> Don Guillett
> > >> Microsoft MVP Excel
> > >> SalesAid Software
> > >> (E-Mail Removed)
> > >> "MSE" <(E-Mail Removed)> wrote in message
> > >> news:CB51A454-35EE-44F9-9B32-(E-Mail Removed)...
> > >> >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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Don Guillett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Send your workbook to my address below and I'll have a look
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "MSE" <(E-Mail Removed)> wrote in message
> news:31A4A3C7-485D-4FC9-9441-(E-Mail Removed)...
>>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" 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
>>> (E-Mail Removed)
>>> "MSE" <(E-Mail Removed)> wrote in message
>>> news4572E32-3865-4261-96C2-(E-Mail Removed)...
>>> > 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").ClearContents
>>> >> Next i
>>> >> End Sub
>>> >> --
>>> >> Don Guillett
>>> >> Microsoft MVP Excel
>>> >> SalesAid Software
>>> >> (E-Mail Removed)
>>> >> "MSE" <(E-Mail Removed)> wrote in message
>>> >> news:CB51A454-35EE-44F9-9B32-(E-Mail Removed)...
>>> >> >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?
>>> >> >
>>> >>
>>> >>
>>>
>>>
>