Changing 2 pivot tables source name per sheet x 15 sheets in Workbook?

[Bobby]

Could someone supply me a sample VBA code to update the source data name of 2 or more PT per sheet(the SOURCE is located in and other Workbook no Access or anything else)?
I have code that changes 1 table in on sheet but not 2.
PLEASE!
Thank's ahead

 

[isabelle]

hi Bobby,

it would be very helpful if you show us your code that changes 1 table in on sheet.

--
isabelle



Le 2012-10-05 19:01, Bobby a écrit :

 

[Bobby]

hi Bobby,



it would be very helpful if you show us your code that changes 1 table inon sheet.

Here it is Isabelle.
Thankyou for your time!

Sub modsource()

Dim pt As PivotTable
Dim ws As Worksheet
tmp = ""
tmp1 = ""
Application.Calculation = xlManual
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.EnableEvents = False
' ActiveSheet.PivotTables("Tableau croisé dynamique2").SaveData = False

For Each ws In ActiveWorkbook.Worksheets
Range("C12").Select
For Each pt In ws.PivotTables
tmp = pt.SourceData
tmp1 = Replace(tmp, "2011-2012", "2012-2013")
Sheets(ws.Name).Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:=tmp1
'ActiveSheet.PivotTableWizard TableName:="


ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable").Visible = False

tmp = ""
tmp1 = ""

Next pt
Next ws
Application.Calculation = xlAutomatic
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub