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?

 

[smw226 via OfficeKB.com]

Hi,

If you pop this at the start it will find the last poulated row...(just
change the "A1".select to the a colum which will be populated all the way
down.

Dim r_lastrow As Integer
Dim current_cell As String

current_cell = ActiveCell.Address
Range("A1").Select

Selection.End(xlDown).Select
r_lastrow = ActiveCell.Row
Range(current_cell).Select

then change your pivot reference to: (and amend C2 to the correct column
reference)

SourceData:="'Duplicates Removed'!R1C1:R" & r_lastrow & "C2"

one thing to note, in your origional example, you missed the Cx from the end
of range statememnt (R1C1:R11356 shoudl be something like R1C1:R11356C2)

HTH

Simon

 

[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
------------------------------­------------------------------­----------------