Excel 2004 and pivot tables

[Chris Waskowich]

I have a workbook that I frequently update - about 3 times a week. The
primary page in it is a worksheet with a pivot table. I update the
contents of the other worksheets in the workbook and then update the
data-range of the pivot table. This all works just fine and
sufficiently in v.X, however, in 2004, when I get into the Wizard and
hit the 'Back' button to change the range, Excel crashes. It is highly
repeatable and seems to do it on any workbook with a pivot table.

Can anyone else verify this? Or, does anyone have a clue as to why
this is happening?

I have to re-create the pivot table *every* time I want to update the
data... which really puts a damper on progress. Also, just as a short
term fix, could I have a VB script or (forgive me for saying this) a
Macro which can update the range of the Pivot table?

 

[JE McGimpsey]

I have a workbook that I frequently update - about 3 times a week. The
primary page in it is a worksheet with a pivot table. I update the
contents of the other worksheets in the workbook and then update the
data-range of the pivot table. This all works just fine and
sufficiently in v.X, however, in 2004, when I get into the Wizard and
hit the 'Back' button to change the range, Excel crashes. It is highly
repeatable and seems to do it on any workbook with a pivot table.

Can anyone else verify this? Or, does anyone have a clue as to why
this is happening?

I have to re-create the pivot table *every* time I want to update the
data... which really puts a damper on progress. Also, just as a short
term fix, could I have a VB script or (forgive me for saying this) a
Macro which can update the range of the Pivot table?

I can't reproduce this with any files with PTs. Does it happen with a
fresh workbook?

If you're just changing the range, you may be interested in using
dynamic ranges instead:

http://www.contextures.com/xlPivot01.html

As for a macro (which is just a script written in VBA, so I'm not sure
what you have against them), start by recording a macro while you
generate your PT. For more help, Jon Peltier has a page of links:

http://peltiertech.com/Excel/Pivots/pivotvba.htm

 

[Chris Waskowich]

I can't reproduce this with any files with PTs. Does it happen with a
fresh workbook?

Interesting. No, it does not happen on a 'fresh' workbook. I've tried
'Save as' to a clean workbook and it still bails. I'll just have to
re-create the entire workbook. Uhg. At least it is not as
catastrophic as I had originally thought.

If you're just changing the range, you may be interested in using
dynamic ranges instead:

http://www.contextures.com/xlPivot01.html

Thanks for the link. It looks like this could help out.

As for a macro (which is just a script written in VBA, so I'm not sure
what you have against them), start by recording a macro while you
generate your PT. For more help, Jon Peltier has a page of links:

http://peltiertech.com/Excel/Pivots/pivotvba.htm

I was referring to the old Excel 4.0 macros. Do people still use
these, or am I the only one ? Thanks for this link too.

 

[Chris Waskowich]

I can't reproduce this with any files with PTs. Does it happen with a
fresh workbook?

If you're just changing the range, you may be interested in using
dynamic ranges instead:

http://www.contextures.com/xlPivot01.html

Well, I created the workbook from scratch and it still bails. I didn't
copy any worksheets over to the new workbook... the only thing I
copied was the data, but I got that from the original source - not the
workbook. So, it must be something about the data that I have that is
causing Excel to get all mad.

I did try out the dynamic data range techique and it worked just fine,
but when I copied the one of the pivot table worksheets to another
worksheet and tried to change the data source, that is when it bailed.

I guess I'm going to have to create each pivot table from scratch...

 

[JE McGimpsey]

Well, I created the workbook from scratch and it still bails. I didn't
copy any worksheets over to the new workbook... the only thing I
copied was the data, but I got that from the original source - not the
workbook. So, it must be something about the data that I have that is
causing Excel to get all mad.

I did try out the dynamic data range techique and it worked just fine,
but when I copied the one of the pivot table worksheets to another
worksheet and tried to change the data source, that is when it bailed.

I guess I'm going to have to create each pivot table from scratch...

If it's not proprietary, I'd be willing to take a look at your workbook.
Since I can't reproduce it, it's hard for me to suggest something...