pivotcache

  1. W

    VBA runtime error 13 - Type mismatch for pivot

    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...
  2. V

    Write ARRAY into PivotCache

    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, -...
  3. T

    Excel Refresh Connection to Access very slow

    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...
  4. J

    VBA Pivotcache + pivottable

    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 &...
  5. T

    Updating Pivot Table Data on Multiple Worksheets/ Pivot Tabs from Different Workbook

    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...
  6. M

    Run Time error 13 - Type Mismatch Error - Creating PivotCache

    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...
  7. M

    Connecting a single Slicer to multiple PivotTables

    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...
  8. L

    Error 1004 when assigning a new value to a Pivot Table .CacheIndex

    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...
  9. D

    how to create a new pivot table based on another pivot table inc. filters/slices etc

    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...
  10. G

    PivotCache is nothing

    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...
  11. R

    Methods to Remove Data Connection but Preserve Data

    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...
  12. C

    [VBA] Average number of days between dates for multiple entries in PivotCache

    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...
  13. C

    VBA create pivotcache from another XL file

    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...
  14. B

    refresh pivottable not completely clearing values?

    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...
  15. G

    VBA CommandText "application-defined or object-defined error"

    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

Watch MrExcel Video

This Week's Hot Topics

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
Top