deleting data but not formulae in multiple sheets
 

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

deleting data but not formulae in multiple sheets
 

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


"Terry Freedman" wrote:

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

deleting data but not formulae in multiple sheets
 

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

"Terry Freedman" wrote:

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

deleting data but not formulae in multiple sheets
 

On Jul 16, 10:13*pm, Charlie
wrote:
...and if it DOES work save a copy as a template for future years!

"Terry Freedman" wrote:
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

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

deleting data but not formulae in multiple sheets
 

On Jul 16, 10:13*pm, Charlie
wrote:
...and if it DOES work save a copy as a template for future years!

"Terry Freedman" wrote:
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

worked brilliantly! Thanks, Charlie :-)

deleting data but not formulae in multiple sheets
 

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.
--


Time is just the thing that keeps everything from happening all at once


"Terry Freedman" wrote:

On Jul 16, 10:13 pm, Charlie
wrote:
...and if it DOES work save a copy as a template for future years!

"Terry Freedman" wrote:
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

worked brilliantly! Thanks, Charlie :-)