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

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
>

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

On Jul 16, 10:13*pm, Charlie <Char...@discussions.microsoft.com>
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

On Jul 16, 10:13*pm, Charlie <Char...@discussions.microsoft.com>
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 :-)

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 <Char...@discussions.microsoft.com>
> 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 :-)
>