Change the source data for all pivots

[griffin]

Hi Experts,

Need your help.I have a source data which is a separate xl file and other separate report file which contains all pivots and i do this report weekly.Now what Im doing is that each time im doing the report i have to change the source range for all the pivots separately one by one which takes time.I know a single pivot cache for all the pivots can save time for me but i cannot change the pivot cache of all pivots as the pivots are many more.

I need to change the source destination of all the pivots at a time.

to do that im trying the below code but it seems its not working or updating the numbers.

Sub change_source()

Dim i As Integer

Dim str As String

textfile = Application.GetOpenFilename("textfile (*.xls),*.xls")

str = Dir(textfile)

Workbooks.Open Filename:=textfile


For i = 1 To Sheets.Count

ThisWorkbook.Sheets("LCC By Project").PivotTables("PivotTable1").PivotCache.SourceData = Workbooks(str).Sheets("AUT TEBIT_FY11-13").Range("a1:cw65536").Address(True, True, xlR1C1, True)


Next i

ThisWorkbook.Sheets(i).RefreshAll

End Sub


Thanks in advance!

 

[griffin]

Hi,


Can any one pls help me on the above query?


Thanks again,

 

[NARAYANK991]

Hi ,


I am not sure that this is the problem , but can you try changing the last statement to :


ThisWorkbook.RefreshAll


Narayan

 

[griffin]

Hi Narayan,


i know the above refreshall code to use but the fact is that my raw data is in other file and the name of the file differs each time based on week so i have to manually change the pivot range all the time which i now want to be automated.


Looking forward.


Thanks,

 

[NARAYANK991]

Hi ,


I do not know what to post ; all I can say is that a solution is likely if you can upload your workbook , with the sheet tabs , the pivot tables and the entire code.


Your For ... Next loop does not seem to use the loop index i anywhere within the loop ; where do we start debugging ?


Narayan