deleting data but not formulae in multiple sheets

[Terry Freedman]

Hi

I need to clear the data in 12 worksheets at once (Jan to Dec), but
leave the formulae intact. Any ideas on a quick way of doing that with
a macro please?

Thanks
Terry

 

[Charlie]

Try something like this, but MAKE A COPY of your workbook first in case it
doesn't work as you expect it to!

Sub ClearCells()
'
Dim Sheet As Worksheet
Dim Cell As Range
'
For Each Sheet In ThisWorkbook.Worksheets
Sheet.Activate
For Each Cell In ActiveSheet.UsedRange
If Left(Cell.Formula, 1) <> "=" Then Cell.ClearContents
Next Cell
Next Sheet
'
End Sub

 

[Charlie]

....and if it DOES work save a copy as a template for future years!

 

[Terry Freedman]

...and if it DOES work save a copy as a template for future years!

Thanks Charlie. I will give this a whirl. Yes, I realise I should have
saved a blank-ish one as a template! Thanks. will let u know how I get
on

 

[Terry Freedman]

...and if it DOES work save a copy as a template for future years!

worked brilliantly! Thanks, Charlie

 

[Neptune Dinosaur]

You could also protect the cells that contain the formula and not the ones
that are to receive data (and protect the Sheets, of course). That would
make it hard for anyone to accidentally (or maliciously) overwrite the
formulae, especially if you passworded the protection.