Coding for Pivot Table

[Help Required!]

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:=xlDatabase,
SourceData:="'Sheet1'!R1C1:R9166C5").CreatePivotTable
TableDestination:="", TableName:="PivotTable1".

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

Many thanks

 

[Tom Ogilvy]

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),CountA(Sheet1!$1:$1))

click the ADD button

now make this the source for your Pivot Table.

 

[bmistry]

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

Sorry - new at this!

 

[Tom Ogilvy]

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