Nasmin Saheed

New Member
Joined
Jun 11, 2015
Messages
49
Office Version
  1. 365
Platform
  1. Windows
Hi All,

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


Thanks
Nasmin
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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
---------------------------
 
Upvote 0
Where does the error occur? I see, though, that you will have to change the way you've define your source data. Try replacing...

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

with

Code:
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...

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

    Worksheets("Pivot").PivotTables("PivotTable1").ChangePivotCache _
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngSourceData)
 
Upvote 0
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...

Code:
    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
 
Upvote 0
Are you still get an error? If so, on which line? And, what type of error?
 
Upvote 0
Hi Sir,

No , Above mentioned code is working fine with me .... Thanks a lots for your coding
 
Upvote 0
That's great, but you only need the following...

Code:
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...

Code:
    Sheets("Pivot").Select

    Range("A6").Select
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top