View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Bobby[_4_] Bobby[_4_] is offline
external usenet poster
 
Posts: 53
Default Batch Pivot Table update

Could someone tell me why if I do a Pivot "Source Change All" with VBA the code does update only 1 source instead of the 2 on 1 specific sheet?

Tab Sidel NAN0 100 Pivot Table dynamique2 C:\Name_fruits\Name\[Name rapports de fruits 2012-2013 .xls]Sidel_Nano'!C1:C20

This is the unchanged source(look at the year):
Tab Sidel NAN0 100 Pivot Table dynamique35 C:\Name_fruits\Name\[Name rapports de fruits 2011-2012 .xls]Sidel_Nano'!C1:C20

This is part of the code:
For Each ws In wb.Worksheets
For Each pt In ws.PivotTables
tmp = pt.SourceData
strSD = Replace(tmp, "2011-2012", "2012-2013")
pt.ChangePivotCache _
wb.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=strSD)
Next pt
Next ws

Thank's ahead!