Hi,

I am relatively new to writing code and in most cases
record a macro which I then manipulate to meet my needs.
So the question I am about to ask may sound very simple to
most of you....!

I have macro that basically collects data from an external
data source. The query has a few parameter queries in it
which allows the user to selet the criteria they want, so
changing the size of the data source each time. The macro
then uses the data to create a pivot table.

The problem I am having is the pivot table uses a hard
coded value for the datasource. As the records in the
datasource can change depending on criteria, the pivot
table can be correct. How can I ensure the datasource can
be dynamically calculated before the pivot table is
created. Currently the code reads as below:

Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:="'Sheet1'!R1C1:R9166C5").CreatePivotTa ble
TableDestination:="", TableName:="PivotTable1".

As I am new at coding, I would appreciate it, if you could
provide detailed instructions.

Many thanks

Use a defined name to define the source data.

Insert =Name = Define

Name := MyData
RefersTo:=
=Offset(Sheet1!$A$1,0,0,CountA(Sheet1!$A:$A),Count A(Sheet1!$1:$1))

click the ADD button

now make this the source for your Pivot Table.

--
Regards,
Tom Ogilvy



"Help Required!" wrote in message
...
Hi,

I am relatively new to writing code and in most cases
record a macro which I then manipulate to meet my needs.
So the question I am about to ask may sound very simple to
most of you....!

I have macro that basically collects data from an external
data source. The query has a few parameter queries in it
which allows the user to selet the criteria they want, so
changing the size of the data source each time. The macro
then uses the data to create a pivot table.

The problem I am having is the pivot table uses a hard
coded value for the datasource. As the records in the
datasource can change depending on criteria, the pivot
table can be correct. How can I ensure the datasource can
be dynamically calculated before the pivot table is
created. Currently the code reads as below:

Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:="'Sheet1'!R1C1:R9166C5").CreatePivotTa ble
TableDestination:="", TableName:="PivotTable1".

As I am new at coding, I would appreciate it, if you could
provide detailed instructions.

Many thanks

How do I make this the source for my pivot table? Directly
in the coding?

Sorry - new at this!
-----Original Message-----
Use a defined name to define the source data.

Insert =Name = Define

Name := MyData
RefersTo:=
=Offset(Sheet1!$A$1,0,0,CountA(Sheet1!$A:$A),Coun tA
(Sheet1!$1:$1))

click the ADD button

now make this the source for your Pivot Table.

--
Regards,
Tom Ogilvy



"Help Required!"
wrote in message
...
Hi,

I am relatively new to writing code and in most cases
record a macro which I then manipulate to meet my needs.
So the question I am about to ask may sound very simple
to
most of you....!

I have macro that basically collects data from an
external
data source. The query has a few parameter queries in it
which allows the user to selet the criteria they want,
so
changing the size of the data source each time. The
macro
then uses the data to create a pivot table.

The problem I am having is the pivot table uses a hard
coded value for the datasource. As the records in the
datasource can change depending on criteria, the pivot
table can be correct. How can I ensure the datasource
can
be dynamically calculated before the pivot table is
created. Currently the code reads as below:

Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:="'Sheet1'!R1C1:R9166C5").CreatePivotTa ble
TableDestination:="", TableName:="PivotTable1".

As I am new at coding, I would appreciate it, if you
could
provide detailed instructions.

Many thanks



.

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:="MyData").CreatePivotTable _
TableDestination:="", TableName:="PivotTable1".


--
Regards,
Tom Ogilvy



"bmistry" wrote in message
...
How do I make this the source for my pivot table? Directly
in the coding?

Sorry - new at this!
-----Original Message-----
Use a defined name to define the source data.

Insert =Name = Define

Name := MyData
RefersTo:=
=Offset(Sheet1!$A$1,0,0,CountA(Sheet1!$A:$A),Coun tA
(Sheet1!$1:$1))

click the ADD button

now make this the source for your Pivot Table.

--
Regards,
Tom Ogilvy



"Help Required!"
wrote in message
...
Hi,

I am relatively new to writing code and in most cases
record a macro which I then manipulate to meet my needs.
So the question I am about to ask may sound very simple
to
most of you....!

I have macro that basically collects data from an
external
data source. The query has a few parameter queries in it
which allows the user to selet the criteria they want,
so
changing the size of the data source each time. The
macro
then uses the data to create a pivot table.

The problem I am having is the pivot table uses a hard
coded value for the datasource. As the records in the
datasource can change depending on criteria, the pivot
table can be correct. How can I ensure the datasource
can
be dynamically calculated before the pivot table is
created. Currently the code reads as below:

Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:="'Sheet1'!R1C1:R9166C5").CreatePivotTa ble
TableDestination:="", TableName:="PivotTable1".

As I am new at coding, I would appreciate it, if you
could
provide detailed instructions.

Many thanks



.