How to write a macro to change data source of a pivot.

[samrat]

Hi,

Can anybody let me know how to write a macro for changing the data source of a pivot?

I need to prepare a file every week by pasting a extract, formating it and then updating the pivot based out of this extract in the same file.

I have automatised the pasting and formating part but not able to change the data source of the pivot, which is essential as the amount of data varies every week.

Thanks
Sam

 

[RTN]

Hi,

This is how I've done it. Hope it can be useful to you.


Sub Update_Pivot_Infra_Source()
Application.ScreenUpdating = False

' Data Infra is the sheet where I have the pivot source
' Cost & Price Total is the sheet where I have the Pivot table

Dim pt As PivotTable

xRowsI = Sheets("Data Infra").Cells(Cells.Rows.Count, 1).End(xlUp).Row
xRangeI = "Data Infra!R66C1:R" & xRowsI & "C24"

Sheets("Data Infra").Select
Sheets("Cost & Price Total").Select For Each pt In ActiveSheet.PivotTables
pt.RefreshTable
pt.SourceData = xRangeI
Next pt

End Sub

//RTN

 

[samrat]

Thanks

I will definitely try this, and get back to you for any further suggestion.

Thanks Again
Sam