ChangePivotCache

[Nasmin Saheed]

Hi All,

I just need a VBA code to Change Pivot Cache. this code should work with any type of excel Version


Thanks
Nasmin

 

[Nasmin Saheed]

Hi All,

I just need a VBA code to Change Pivot Cache. this code should work with any type of excel Version


Thanks
Nasmin

My VBA code is as below

Sheets("Invoice").Select
Range("C13").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select


DataArea = "Invoice!R13C3:R" & Selection.Rows.Count & "C" & Selection.Columns.Count
Worksheets("Pivot").PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=DataArea, Version:=xlPivotTableVersion15)

Sheets("Pivot").Select
Range("A6").Select
ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh


but getting error as ---------------------------
Microsoft Visual Basic for Applications
---------------------------
Compile error:


Variable not defined
---------------------------
OK Help
---------------------------

 

[Domenic]

Where does the error occur? I see, though, that you will have to change the way you've define your source data. Try replacing...

[COLOR=#574123]DataArea = "Invoice!R13C3:R" & Selection.Rows.Count & "C" & Selection.Columns.Count[/COLOR]

with

DataArea = Selection.Address(external:=True)

Also, if your range is bounded by any combination of blank rows and blank columns, you can define the range for your source data using the CurrentRegion property. So your code can be re-written as follows...

    Dim rngSourceData As Range
    
    Set rngSourceData = Worksheets("Invoice").Range("C13").CurrentRegion

    Worksheets("Pivot").PivotTables("PivotTable1").ChangePivotCache _
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngSourceData)

 

[Nasmin Saheed]

Also, if your range is bounded by any combination of blank rows and blank columns, you can define the range for your source data using the CurrentRegion property. So your code can be re-written as follows...

    Dim rngSourceData As Range
    
    Set rngSourceData = Worksheets("Invoice").Range("C13").CurrentRegion

    Worksheets("Pivot").PivotTables("PivotTable1").ChangePivotCache _
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngSourceData)

[/QUOTE]

Hi Sir,

No, my range is not having Blanks .. but i have tried your 3rd code and it is working fine .. now my code is as below .... if still any correction required , plz advice me

Sub Pivot_Testing()




Sheets("Invoice").Select
Range("C13").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select




Dim rngSourceData As Range

Set rngSourceData = Worksheets("Invoice").Range("C13").CurrentRegion


Worksheets("Pivot").PivotTables("PivotTable1").ChangePivotCache _
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngSourceData)


Worksheets("Pivot").PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngSourceData, Version:=xlPivotTableVersion15)

Sheets("Pivot").Select
Range("A6").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh


End Sub

 

[Domenic]

Are you still get an error? If so, on which line? And, what type of error?

 

[Nasmin Saheed]

Hi Sir,

No , Above mentioned code is working fine with me .... Thanks a lots for your coding

 

[Domenic]

That's great, but you only need the following...

Sub Pivot_Testing()

    Dim rngSourceData As Range
    
    Set rngSourceData = Worksheets("Invoice").Range("C13").CurrentRegion
    
    Worksheets("Pivot").PivotTables("PivotTable1").ChangePivotCache _
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngSourceData)


End Sub

Of course, if your intention is to make active your sheet named "Pivot" and to select cell A6, you can add these lines at the end of the procedure...

    Sheets("Pivot").Select

    Range("A6").Select