Create a pivot table in a new workbook from data in another workbook

Question

I want to create a pivot table in a new workbook from data selected in another workbook.

I have tried the code from this answer but I get a reference error "1004" in the last line of code.

Sub test() Dim wb As Workbook Dim ws As Worksheet Dim pRange As Range Dim pc As PivotCache Dim pt As PivotTable Dim InitialPivotCache As PivotCache Dim CopyPivotCache As PivotCache Set wb = Workbooks.Add Set ws = wb.Worksheets(1) Set pRange = ThisWorkbook.Sheets(1).Range("A1:B6") Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, pRange) 'Cache in ThisWorkbook Call CountCaches Set CopyPivotCache = wb.PivotCaches.Create(pc.SourceType, pc.SourceData) Set pt = ws.PivotTables.Add(CopyPivotCache, Range("A3"), "MyPivotTable") End Sub

I Counted number of caches with this code in my thisworkbook after I have set the "pc" cache I found 0 caches.

Sub CountCaches() MsgBox "There are " _ & ThisWorkbook.PivotCaches.Count _ & " pivot caches in the active workook." End Sub

I don't know what's wrong in my code. How can I fix it?

A quick question - why are you creating a pivot table in a seperate workbook? If anything, that should be a normal table. Pivot tables are supposed to be directly referring to an internal data source — Samuel Hulla
– Samuel Hulla, Commented Jun 6, 2018 at 9:06
I want my user to select data file (worksheet) and do all my calculation in another workbook. It's more clean. — youssef mhiri
– youssef mhiri, Commented Jun 6, 2018 at 9:13
I can tell you from my personal coding experience, that it's not "more clean". If anything, you are going to cause interconnectivity issues doing this. If you need to showcase data for some basic user editing a simple table (ListObject) will do. — Samuel Hulla
– Samuel Hulla, Commented Jun 6, 2018 at 9:14
I want to use pivot table just to make a groupby name list in my rows and then sum values in another column. Is there another way to do it fast without pivot tables? — youssef mhiri
– youssef mhiri, Commented Jun 6, 2018 at 9:16

Lee Li Fong · Accepted Answer · 2018-06-06 09:47:50Z

Sub CreatePT() Set wb = Workbooks.Add 'Create new workbook Set ws = wb.Worksheets(1) pRange = "[" & ThisWorkbook.Name & "]" & Sheet15.Name & "!" & ThisWorkbook.Sheets(15).Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1) ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=pRange). _ CreatePivotTable TableDestination:="[" & wb.Name & "]" & ws.Name & "!R1C1", TableName:="PivotTable4" End Sub

Collectives™ on Stack Overflow

Create a pivot table in a new workbook from data in another workbook

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related