pivotcache

Below is my code and I am getting type mismatch or object not defined error while running it. Error is at "Set Pcache" line. Code still creates a empty pivot table and throwing an error. Can someone help me with this. Any other suggestion to below version is always welcomed...

How can an Array of data be written into a Pivot Tables PivotCache? The worksheet: "Actuals" has a pivot table called: "PvtTransByMonth" The current PivotTable's PivotCache / Source has the fields: - Source -> xlPageField, - Category -> xlRowField, - Year -> xlColumnField, -...

Hi, I have a table in Excel that links to a table in Access which is taking a very long time (>45mins) to refresh. Table size: 252 fields/columns, 11,000+ rows/records of various data types in a single table. There are no additional calculated fields in the table. Pivots/PivotCache using the...

Running code, but it debugs at statement dim pt as pivottable: Compile error: A module is not a valid type?? sub createpivottable () dim pc as pivotcache dim pt as pivottable set pc = thisworkbook.pivotcaches.create(sourcetype:=xldatabase, sourcedata:= sheet1.name &...

Hello, First, my apologize if this is in the wrong area and/or this has already been answered. This is my first time posting and I am relatively inexperienced with VBA. What I am trying to figure out is how to do the following: 1. Lookup user inputs on the ActiveWorkbook for new workbook...

I am having an issue with my coding for creating a pivotcache - It worked perfectly in another spreadsheet; however, an update was done to the data and the column order changed so I needed to tweak some other code. I never touched the PTCache coding and it works perfectly in the other...

I am using Excel 2010 for the following: I have ten Pivot tables on a "Dashboard" sheet. I want to create a slicer that filters all ten pivot tables. When I try to connect the pivot tables to the slicer the first one works but the second one fails with the 1004 error. I checked to see if all...

Hello, everyone! This is my first post here, because it's the first time I did not find a satisfactory answer to an issue... The problem is, I have a file with 21 sheets, each one has 3 or 4 pivot tables; all pivot tables INSIDE A SHEET connect to a specific data source (which is a range in...

Hi, I have a master sheet which houses a dynamic pivot chart/graph along with various filters and slicers. Once I have applied the relavent filters and slicers to the pivot - I want to be able to "copy" this pivot table/chart to a separate sheet - using a separate datacache - but preserving...

Hello, I am needing assistance with PivotCaches. I am including code below that is intended to have access export qryExport to excel, then the code is supposed to create multiple pivots on the Pivots sheet based on the qryExport. I have gotten it to work a couple times, but not consistently...

If I run this vba it removes the external data connection to the pivot table: ActiveWorkbook.Connections("EXAMPLE").Delete When I attempt to drill down into the pivot table it states the data connection was removed. Is there a way to remove the data connection but preserve the data in the...

Hi everybody, my current task is to evaluate data from a MSSQL-DB. This is done by creating a pivot cache, from which pivot charts are generated. This works fine ;) Every DB-entry has a field with a start date and a field with a end-date. My task is to get the number of days between those two...

This maybe too ambitious. I have 2 workbooks. Workbook A has new data added weekly and Pivot tables refreshed. I would like Workbook B to refresh its Pivot tables from the data in Workbook A. I am newish at VBA, I have been trying to work out how to use the PivotCaches.Create method to Get...

I have a pivottable. pivotcache data gets updated and new rows added. I have function to update datasource of each pivottable, and then it refreshes the pivottable. I have another function that goes through all statuses (which are column data items) and creates a separate detail sheet for...

Sub QueryUpdate() Sheets("Totals").PivotTables("PivotTable1").PivotCache.CommandText = "SELECT * FROM Totals" End SUB but it gives me the error: "application-defined or object-defined error" Any idea how I can make this work? Thanks