"n" <n@N wrote in message ...

i have tried that after your advice, but the clearcontents
doesnt work, cause there are pivot tables there. I get
the message "Can not change this part of pivot table"

Any ideas
TIA



ummmm - change the reference to "Pivot YTD" to another sheet
then delete the pivot sheet. When you restart you can recreate the
pivoted sheet and change the references back.

OR

replace all the "=" on the sheet where you are getting the #REF errors
with something like "xxxxxx". After you recreate the pivot you can change
all "xxxxxx" to "="

OR

have the sheet with the #REFs on it in a different workbook to the
pivot table sheet. You close the sheet where you get the #REFF errors
first. Delete the pivot table - save that workbook. When you want to
start up again you open the pivot table workbook and recreate it then,
after the pivot table is created, you open the other workbook. The
links will work cos they do not care what you do when the workbook
which contains them is closed.

You can even open the workbook with the references to the pivot table,
after the pivot table is deleted, and see the results AS THEY WERE when
you last had a pivot table saved AS LONG AS YOU DO NOT refresh
the links to the closed workbook. If you accidentally do refresh the links
then you will get your #REF errors but that is ok cos you can just close the
workbook with them in it and not save it.

Thinking about it further - the two workbooks approach is probably the
best as it means you do not have to do extra work than is absolutely
necessary on a regular basks. You only need to right click the tab of
that worksheet and move it to a new workbook. You can either open
both workbooks and view things like they were in one workbook or
you can open either by itself - the workbook with the pivot table will
need to be opened on its own when you want to change the pivot table
(recreate it) - the workbook with the links to the pivot table will need to
be opened on its own when you want to view the results of the pivot
table in-between when you want it changed.

Chrissy.