Changing Pivot Range Programatically - header row issue
 

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
--
(To e-mail me, just remove any color names from my posted e-mail, as well as
any extra periods)

Changing Pivot Range Programatically - header row issue
 

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.

--
Regards,
Tom Ogilvy

"Scott Lyon" wrote in message
...
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
--
(To e-mail me, just remove any color names from my posted e-mail, as well
as
any extra periods)

Changing Pivot Range Programatically - header row issue
 

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

(To e-mail me, just remove any color names from my posted e-mail, as well as
any extra periods)

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Scott Lyon" wrote in message
...
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
--
(To e-mail me, just remove any color names from my posted e-mail, as
well
as
any extra periods)

Changing Pivot Range Programatically - header row issue
 

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","Exclud e")

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.

--
Regards,
Tom Ogilvy


"Scott Lyon" wrote in message
...
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
--

(To e-mail me, just remove any color names from my posted e-mail, as well
as
any extra periods)

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Scott Lyon" wrote in message
...
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
--
(To e-mail me, just remove any color names from my posted e-mail, as
well
as
any extra periods)