Changing Pivot Range Programatically - header row issue

[Scott Lyon]

I've been reading the posts here concerning changing the range of a pivot
table, and none have answered my question, so I thought I'd post it and see.

To begin, I have one worksheet that contains a table of data (with a header
row). On another worksheet, there is a pivot table based on that data.

The user should be able to make a selection (from a Combo Box control added
to the worksheet), and based on that selection, it will change the range of
data to be used for the pivot table.

I figured I'd use the PivotTable.SourceData field to do this. The only
problem I see is the header row.

You see, my header row is Row 1. But the user might select a range of (for
example) the data in rows 30-50 (which obviously would not have the header
row).

If I understand correctly, the Pivot Table requires that the first row be
the header row.


You see my dilemma?


Can anyone offer any suggestions, short of having to manipulate the data
each time and insert an extra "header" row for each range needed?


Thanks!

-Scott

 

[Tom Ogilvy]

If you are looking at subsets of a single data table, I would put in a dummy
column where you would enter one value if the record is to be included and
another value if not. When the user makes the selection, write the
appropriate values, then refresh the pivot table. You would need a page
field for this column that is set to the value used for including records.

 

[Scott Lyon]

What I decided to try, since I had the data on one worksheet, and the pivot
table on another worksheet (with nothing else), is to simply add code to
copy the data fields to the worksheet with the pivot table (just scrolled
down to be off-screen), into a series of cells right below another copy of
the "header" fields.

Then I use that data (which now includes the header) as the SourceData of
the PivotTable. Specifically, since my PivotTable starts at cell A3, the
code I have is as follows (where sSource is a string set up to look like
"Sheet2!R30C2:R40C8" except with the end row set programatically).

Range("A3").Select
ActiveCell.PivotTable.SourceData = sSource

So far this seems to ALMOST work. The problem is after it's done, it still
leaves up the PivotTable Field List window (like I used in the wizard to
create the Pivot Table initially). How can I update the source without
getting that window?

Any ideas? What am I missing/forgetting?


-Scott

 

[Tom Ogilvy]

If you use my method, you don't have to update the sourcedata.

You could put a formula in the dummy column and just write your row values
to cells referred to by the formulas - then refresh the table.

something like

=if(And(row()>=$M$1,row()<=$M$2),"Include","Exclude")

or if you would be happy with true or false

=And(row()>=$M$1,row()<=$M$2)


Pretty simple, actually.

Never heard of the problem you are describing.