Creating Pivot through VBA

  • Thread starter Thread starter Chetu
  • Start date Start date
C

Chetu

Hi I have an excel sheet linked to a database it gets updated every
week. I need to run a Pivot to get only the details of Supplier no.,
Material no and Product class out of 10 columns. The Pivot should come
in a new sheet. The number of rows will be dynamic.

Can anyone please give me a code with explanations.
 
you don't need to do this with code
excel 2003
select the table, from the menu Data, select Pivot Table and follow the
instructions

excel 2007
from the Insert tab, the tables group, select Pivot Table and follow the
instructions

when the data is refreshed, just refresh the pivot table

Now, if the data table is of varying size when its refreshed, you'll need to
ensure that the pivot table source data refreshes with all the new data

here's an example of a code module that refreshes a simple pivot table ...
copy it into a standard code module. To do this, open the development
environment (ALT+F11) then Insert/Module

Option Explicit
Sub RefreshPivotTable()

Dim PT As PivotTable
Set PT = Worksheets("Sheet1").PivotTables(1)
With Worksheets("Price lookup")
PT.SourceData = "'Price lookup'!R1C1:R" &
..Range("a1").End(xlDown).Row & "C3"
End With
Set PT = Nothing

End Sub
 
Hi Patrick,

Thank you very much for the replly, I wanted to first refresh the data
and then copy paste only the values in other sheet. How can i achieve
it?

Regards,
Chetan.J
 
Is it possible to have code refresh a pivot table after the source data sheet
completes it's refresh from the exteral source?
 
Back
Top