clear contents on linked sheet
 

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

clear contents on linked sheet
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

clear contents on linked sheet
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Try this from anywhere in the workbook withOUT selections.
Sub clearshts()
sheet1.usedrange.clearcontents
sheet3.Rows("15:65").SpecialCells(xlCellTypeConsta nts).ClearContents
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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