Pivot Table Data Source - External to Internal

mamafich

New Member
Joined
Sep 17, 2013
Messages
3
Hi everyone,

I've been searching to find a way of changing my pivot table data source from Access file to a local excel sheet (of same file). Is that anyhow possible?

When I select "Change Data Source" From PivotTable Tools / Options, it only allows me to select the second option, "Use an external data source".

I've tried to do it on VBA as well, but I guess I have some points missing therefore I got error. I've used below code and it gives the error - "Run-time error 5 - invalid procedure call or argument"

Code:
    ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
         PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R89C64:R1265C120", Version:=xlPivotTableVersion14)
Could you please help me if it's possible? I've set all charts and calculations based on that pivot table, and I really don't want to start from the beginning.

Thanks a lot!
Cheers,

Selim
 

mendosi

New Member
Joined
Oct 9, 2014
Messages
3
any suggestions?
It's been a while since you asked, but I had this same problem and just discovered a solution.

1. Create a new pivot table based on the new data source (it can just be empty).
2. Click in the new pivot.
3. In the immediate window type "? selection.pivottable.cacheindex" and press enter.
4. Note the index number of the pivot cache.
5. In Excel click in the old pivot which you want to change.
6. In the immediate window type "selection.pivottable.cacheindex = x" where x is the index number you noted in step 4.
7. Repeat step 6 for every pivot table which you want to use the new source.
8. Remove the table you created in step 1.

There may to change all tables from one cache to another in a single step, but it's probably not something you need to do often.
 

nflami

New Member
Joined
Apr 16, 2014
Messages
25
It's been a while since you asked, but I had this same problem and just discovered a solution.

1. Create a new pivot table based on the new data source (it can just be empty).
2. Click in the new pivot.
3. In the immediate window type "? selection.pivottable.cacheindex" and press enter.
4. Note the index number of the pivot cache.
5. In Excel click in the old pivot which you want to change.
6. In the immediate window type "selection.pivottable.cacheindex = x" where x is the index number you noted in step 4.
7. Repeat step 6 for every pivot table which you want to use the new source.
8. Remove the table you created in step 1.

There may to change all tables from one cache to another in a single step, but it's probably not something you need to do often.

I know this thread's old but it was extremely helpful and I didn't know what immediate window meant, so I thought I'd elaborate as I was caught up on step 3 for a few minutes.

The immediate window is located within the Visual Basic Editor window, you can open it with Alt+F11
Once the Visual Basic window is open Ctrl+G will launch the Immediate window, now you can type steps 3 and 6 in here.
 

jxinyi09

New Member
Joined
Jun 13, 2017
Messages
1
Great thanks for the notes! It is indeed extremely helpful and save me a lot of time!

I know this thread's old but it was extremely helpful and I didn't know what immediate window meant, so I thought I'd elaborate as I was caught up on step 3 for a few minutes.

The immediate window is located within the Visual Basic Editor window, you can open it with Alt+F11
Once the Visual Basic window is open Ctrl+G will launch the Immediate window, now you can type steps 3 and 6 in here.
 

istiasztalos

Board Regular
Joined
May 18, 2016
Messages
135
Hi,

It worked perfectly to change the source, but it also deleted all the calculated items I had configured.... just fyi in case others find this thread later
 

Forum statistics

Threads
1,081,530
Messages
5,359,347
Members
400,524
Latest member
Excelbat

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top