Macro
 

Hi,

I have set up a macro which picks up some data from an
external database, prompts user for citeria and then
presents the data on a worksheet. It then creates a Pivot
Table using the data presented.

However, as the user can select different types of critera
(ie. Date range) the number of records can be different
each time. Therefore the data for the pivot table
increases or decreases. How can I code this to incorporate
the discrepancy? Currently I have this....

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
"'Raw Data'!R1C1:R9166C5").CreatePivotTable
TableDestination:="", TableName _
:="PivotTable1"

The "R1C1:R9166C5" means that only records up to Cell 9166
are read. The rest are ignored and not included in the
Pivot Table.

Can any one help?

Many thanks

Macro
 

I have a few sheets with pivottables based on a dynamic
named range. Insert a named range called "MyRange" with
the following equation:

=Offset('Raw Data'!A1,0,0,counta('Raw Data'!$A:$A),5)

Then change the relevant lines to:

Sourcedata:= "'Raw Data'!MyRange"

That might help.

Pete


-----Original Message-----
Hi,

I have set up a macro which picks up some data from an
external database, prompts user for citeria and then
presents the data on a worksheet. It then creates a Pivot
Table using the data presented.

However, as the user can select different types of
critera
(ie. Date range) the number of records can be different
each time. Therefore the data for the pivot table
increases or decreases. How can I code this to
incorporate
the discrepancy? Currently I have this....

ActiveWorkbook.PivotCaches.Add(SourceType:=xlData base,
SourceData:= _
"'Raw Data'!R1C1:R9166C5").CreatePivotTable
TableDestination:="", TableName _
:="PivotTable1"

The "R1C1:R9166C5" means that only records up to Cell
9166
are read. The rest are ignored and not included in the
Pivot Table.

Can any one help?

Many thanks

.