Clear a worksheet without clearing formulas?
 

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

Clear a worksheet without clearing formulas?
 

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

"doodah" wrote in message
...
Have a worksheet been using; don't have a blank master. Now i need a blank
worksheet with the formulas still intact.

Clear a worksheet without clearing formulas?
 

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?


--
kghexce
------------------------------------------------------------------------
kghexce's Profile: http://www.excelforum.com/member.php...o&userid=29804
View this thread: http://www.excelforum.com/showthread...hreadid=497240

Clear a worksheet without clearing formulas?
 

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

"kghexce" wrote in
message ...

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?


--
kghexce
------------------------------------------------------------------------
kghexce's Profile:
http://www.excelforum.com/member.php...o&userid=29804
View this thread: http://www.excelforum.com/showthread...hreadid=497240

Clear a worksheet without clearing formulas?
 

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.


--
kghexce
------------------------------------------------------------------------
kghexce's Profile: http://www.excelforum.com/member.php...o&userid=29804
View this thread: http://www.excelforum.com/showthread...hreadid=497240

Clear a worksheet without clearing formulas?
 

Paul B, That worked for me until i got to delete. Here's what I did instead:
Edit, go to, special, constants, ok. THEN right click, clear contents.

I found that if you hit delete at the end it clears the formulas but if you
click on clear contents it leaves the formulas intact. Thank you for getting
me that far, though.

"Paul B" wrote:

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

"doodah" wrote in message
...
Have a worksheet been using; don't have a blank master. Now i need a blank
worksheet with the formulas still intact.