changing source data of pivot table

Question

Hi All,

I get a message when trying to change source data of pivot table with this line:

    ActiveSheet.PivotTables("pivotname").ChangePivotCache _

        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

        "DUMP!R1C1:R" & Dumpcount & "C25", Version:= _

        xlPivotTableVersion14)

The message:

"Do you want to replace the contents of the destination cells in [workbook_name]sheet_name?

Yes/No"

[workbook_name]sheet_name is the sheet where the pivot table is located.

The columns of the pivot table disappear either if I reply Yes or No.

As far as I could observe the column structure of the source did not change, only the number of rows has increased.

Why does this happen and how can I avoid it. What do I do wrong, what did not I notice?

Thanks,

Stefi

Answer 1

I got an answer to the first part of the problem on http://social.technet.microsoft.com/Forums/en-US/excel/threads from Learning and Learning, and finally figured out the answer to the second part myself.

The cause of disappearing columns was this, maybe it is of interest also for other people: Some of the column headers had a redundant trailing space. When the pivot table was built Excel put these headers into the field list trimmed, but when datasource was changed to another range with the same headers with redundant trailing spaces Excel tried to match the trimmed field names and the untrimmed headers, found they are different and removed pivot table columns. Trimming headers before changing  datasource resolved the problem.

Stefi

Answer 2

For issues related to Pivot tables you may also post your question and queries on the link below.

http://social.technet.microsoft.com/Forums/en-US/excel/threads