SPECIFYING SOURCE DATA INTO A PIVOT TABLE

[Sheron Donaldson]

Hi

I want to create a macro, that is going to be used on a
weekly basis, to create a pivot table. The source data is
going to be changing every week, (e.g. this week the
source data will go upto row 200, but next week the data
will go up to 150).

What do I need to put into my macro for it to take account
of the change every week. As it is currently it is always
going to row 200, every week but I want it to change as
the data changes.

Cheers
Sheron

 

[Debra Dalgleish]

Use a dynamic name for the data source, and use that name in the macro.
For example, with a data source named "Database":

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

There are instructions on my web site for using a dynamic data source:

http://www.contextures.com/xlPivot01.html#Dynamic