1. 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, -...
  2. 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...
  3. 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 &...
  4. 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...
  5. 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...
  6. 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...
  7. 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...
  8. 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...
  9. 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...
  10. 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...
  11. 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...
  12. 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...
  13. 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...
  14. 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

Some videos you may like

This Week's Hot Topics