Creating a dyanmic pivot table

[Mistry]

I am in the process of creating a macro which will be able to select a
table and create a pivot table based on that data. The data changes on
a weekly basis. ie the number of rows. How do i create a table based
on this The code i have seem to only accept "hard coded" data ranges.

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Duplicates Removed'!R1C1:R11356").CreatePivotTable
TableDestination:="", _
TableName:="PivotTable3"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable3").SmallGrid = False
ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:= _
"Company Code Name", ColumnFields:="Status of Response"
ActiveSheet.PivotTables("PivotTable3").PivotFields("Status of
Response"). _
Orientation = xlDataField

I have seen something on Dynamic ranges but not sure how or if this
will work. Any ideas?

 

[Roger Govier]

Hi

On your sheet Duplicates Removed, create a named range
Insert>Name>Define>Name> Myrange

Refers to =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))

Then change in your code
SourceData="'Duplicates Removed'!Myrange"

 

[Ken Wright]

Have you considered using an existing file as a template. Get all your
Pivot tables in place, use a dynamic range to get the data and then just
paste the new data each time over the old and refresh the tables?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------