Creating a dyanmic pivot table

  • Thread starter Thread starter Mistry
  • Start date Start date
M

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?
 
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
 
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))
Click to expand...

Then change in your code
SourceData="'Duplicates Removed'!Myrange"
 
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 :-)
------------------------------­------------------------------­----------------
 
Back
Top