locked
Changing the Data Source for a pivot table using VBA RRS feed

  • Question

  • I am trying to change the data source on a pivot table in my macro but keep getting a run time error saying the PivotTable field is not valid.....

    I know the data I'm using has all columns labeled and I'm not changing the name of any PivotTable fields

    I looked at another thread on this subject and I tried using their suggestion, but I must be missing something. Below is my code for the pivot. Any suggestions would be greatly appreciated. 

     Sheets("Pivot").Select
        DataSht = "SHIPPERS (2)!A1:" & Cells(lngLastRow, lngLastCol).Address
        ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
            PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
             DataSht, Version:=xlPivotTableVersion14)

    Thank You,

      JT

    Wednesday, March 26, 2014 4:41 PM

Answers

  • Hi JT,

    Welcome to MSDN forum.

    A quick way to check the code is recording a macro to get VBA code.

    I tried changing the data source on a pivot table, I would get the code as below:

    ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
            PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "D:\[Book1.xlsx]Pivot!R1C1:R7C3", Version:= _
            xlPivotTableVersion14)
    

    So the issue is caused by SourceData.

    Here is the code I changed from yours:

    DataSht = ActiveWorkbook.Path & "\[" & ActiveWorkbook.Name & "]" & "Pivot!R1C1:R" & lngLastRow & "C" & lngLastCol
        ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
             PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
              DataSht, Version:=xlPivotTableVersion14)
    

    It works fine for me.

    Hope this help.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by George Hua Friday, April 4, 2014 10:26 AM
    Thursday, March 27, 2014 5:28 AM