WinXP Pro MSOffice 2003

I am trying to use the following procedure to clear both worksheets so
they are ready to produce a fresh report.

These worksheets are linked together on one column, so I need to clear
Sheet3 first to break the links so that I can clear Sheet1.

The second part of the procedure clears sheet1 just fine except it can't
clear the linked col because data is still in that col on sheet3.
The problem is when I step into the procedure and go thru the first part
which is to clear sheet3, it gives me an error (application-defined or
object-defined error) on the 'cells.select' line and stops. I recorded a
macro to get the code for selecting the entire sheet, and 'cells.select'
is what recorded.

Clearly I am missing something here, but I haven't a clue what it might
be.
Could someone please give me a nudge?
BTW, this procedure is a macro applied to a button on sheet1

Public Sub Clear()
' Clear Worksheet values for next report

Sheet3.Activate
Cells.Select
Selection.ClearContents

Sheet1.Activate
Sheets("Sheet1").Select
Rows("15:65").Select
Range("A65").Activate
Selection.SpecialCells(xlCellTypeConstants).Select
Selection.ClearContents
End Sub

Thank you
Joanne

Try this from anywhere in the workbook withOUT selections.
Sub clearshts()
sheet1.usedrange.clearcontents
sheet3.Rows("15:65").SpecialCells(xlCellTypeConstants).ClearContents
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Joanne" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> WinXP Pro MSOffice 2003
>
> I am trying to use the following procedure to clear both worksheets so
> they are ready to produce a fresh report.
>
> These worksheets are linked together on one column, so I need to clear
> Sheet3 first to break the links so that I can clear Sheet1.
>
> The second part of the procedure clears sheet1 just fine except it can't
> clear the linked col because data is still in that col on sheet3.
> The problem is when I step into the procedure and go thru the first part
> which is to clear sheet3, it gives me an error (application-defined or
> object-defined error) on the 'cells.select' line and stops. I recorded a
> macro to get the code for selecting the entire sheet, and 'cells.select'
> is what recorded.
>
> Clearly I am missing something here, but I haven't a clue what it might
> be.
> Could someone please give me a nudge?
> BTW, this procedure is a macro applied to a button on sheet1
>
> Public Sub Clear()
> ' Clear Worksheet values for next report
>
> Sheet3.Activate
> Cells.Select
> Selection.ClearContents
>
> Sheet1.Activate
> Sheets("Sheet1").Select
> Rows("15:65").Select
> Range("A65").Activate
> Selection.SpecialCells(xlCellTypeConstants).Select
> Selection.ClearContents
> End Sub
>
> Thank you
> Joanne

should have been
sheet3.usedrange.clearcontents
sheet1.Rows("15:65").SpecialCells(xlCellTypeConstants).ClearContents

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Don Guillett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Try this from anywhere in the workbook withOUT selections.
> Sub clearshts()
> sheet1.usedrange.clearcontents
> sheet3.Rows("15:65").SpecialCells(xlCellTypeConstants).ClearContents
> End Sub
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Joanne" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> WinXP Pro MSOffice 2003
>>
>> I am trying to use the following procedure to clear both worksheets so
>> they are ready to produce a fresh report.
>>
>> These worksheets are linked together on one column, so I need to clear
>> Sheet3 first to break the links so that I can clear Sheet1.
>>
>> The second part of the procedure clears sheet1 just fine except it can't
>> clear the linked col because data is still in that col on sheet3.
>> The problem is when I step into the procedure and go thru the first part
>> which is to clear sheet3, it gives me an error (application-defined or
>> object-defined error) on the 'cells.select' line and stops. I recorded a
>> macro to get the code for selecting the entire sheet, and 'cells.select'
>> is what recorded.
>>
>> Clearly I am missing something here, but I haven't a clue what it might
>> be.
>> Could someone please give me a nudge?
>> BTW, this procedure is a macro applied to a button on sheet1
>>
>> Public Sub Clear()
>> ' Clear Worksheet values for next report
>>
>> Sheet3.Activate
>> Cells.Select
>> Selection.ClearContents
>>
>> Sheet1.Activate
>> Sheets("Sheet1").Select
>> Rows("15:65").Select
>> Range("A65").Activate
>> Selection.SpecialCells(xlCellTypeConstants).Select
>> Selection.ClearContents
>> End Sub
>>
>> Thank you
>> Joanne
>