clear the cells--but NOT the formulas-- 4

[JTuc]

I made a big spreadsheet which has subtotaled rows and subtotaled columns. It was used to record data for the entire year. Now I want to repeat this for 2003 with the new data that I will be accumulating. Can I copy and paste my 2002 spreadsheet leaving all cells blank except the headings, but leave all of the formulas intact? I hate to think I'll have to start over again from square one. It took hours and hours to create the 2002 spreadsheet because of the quantity of items I'm tracking.

 

[ETID]

Select the entire sheet,


Then from menu Edit>Copy


then in new sheet cell A1 Edit>Paste Special>...select the formulas button.>then "ok"


repeat to copy any formats if needed.

 

[Zathras]

When I tried ETID's solution in Excel 97, it copied the input cells as well as the formulas. What it didn't copy was cell formats, colors, borders, etc.

Try the following macro to see if it does what you want.

=====================================================

Sub ClearDataEntryCells()

' Clear all data-entry cells
' Keep formulas, formatting, colors and borders

Dim r As Range
For Each r In ActiveSheet.UsedRange
  If Not r.HasFormula Then
    If Not r.PrefixCharacter <> &quot;&quot; Then
      r.ClearContents
    End If
  End If
Next r
End Sub

=======================================================

Historical note: In the old days of Lotus 1-2-3 it was possible to do what you wanted by subtracting the worksheet from itself. The formulas were ignored. Unfortunately, Excel doesn't seem to work that way. Too bad.

 

[xlhelp]

While in your spreadsheet, hit F5, click Special, select Constants, click OK. Press Delete on your keyboard. -Radix lecti

 

[Zathras]

Very nice. "Go To" for an edit operation. What was Microsoft thinking?

One minor point, be sure to un-check "Text" in order to retain the headings.

 

[JTuc]

Thanks all! The tip from XLhelp worked for me. Zanthras, I believe yours may also work, but your answer was much too advanced for me, my fault not yours. The uncheck the text comment was a big help though. Thanks again!