Clear a worksheet without clearing formulas?

D

doodah

Have a worksheet been using; don't have a blank master. Now i need a blank
worksheet with the formulas still intact.
 
P

Paul B

doodah, try this, Edit, go to, special, constants, OK, delete

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
K

kghexce

Have a similar issue...

Summary page with titles, fixed data, and variable data entered as
needed PLUS 750 linked pages each of which has titles, fixed data and
variable data entered. By following Pual B's suggestion and
highlighting to the right and below titles and fixed data the Summary
page is easily blanked. (Thanks Paul B).

Would also like to clear the entries in Cells B2:C21 on each of the 750
pages without affecting the column titles and other constants on each
page.

Plan had been clear one page manually, copy cells A1:C21 then CRTRL
PAGE DOWN to next Sheet and PASTE via macro 749 times in a row. (This
will only take about 30 minutes once a year so it isn't a big deal and
is how the pages were created in the first place).

However if there is a slicker solution that would be great.

Any ideas?
 
P

Paul B

kghexce, here is one way,

Sub Clear_Range()
'Will clear whats in B2:C21 in all sheets
'except sheet named Summary
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name <> "Summary" Then
With ws
.Range("B2:C21").ClearContents
End With
End If
Next ws
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
K

kghexce

Know nothing about VBA and thus just added the code to an existing
module, resaved it and ran. Must have done something wrong as it just
worked and worked and worked. Needed to kill via Task Manager.

Did the 'clear one sheet, copy and paste' via macro 750 times.
Surprisingly, it took less than 10 minutes to empty all the sheets.
(Saved a blank version as a template for 2007).

My New Year's resolution is to read the recently acquired 'VBA for
Dummies'.

"I'll Be Back!" once the book is read and try again.

Thanks so much for the tip.
 
Top