My questions / problems here are: 1) Do I actually need the lines or can I set the filter also without it ?
2) Can I check before setting the filter if the corresponding item (e.g. Otherwise I would try to filter for something that doesn't exist (yet).
This morning’s post was prompted by a question posted at Mr Excel (here) regarding changing the source data for multiple pivot tables to refer to a different worksheet range.
Display Alerts = True End Sub This chart is based on the data on the Table worksheet of the sample workbook. Plot By = xl Rows End If ' Prompt the user for a title. If the number of columns is greater, the values in the first row are selected as categories. Count If r c Then Set obj Categories = Range(Cells(1, 1), Cells(r, 1)) Else Set obj Categories = Range(Cells(1, 1), Cells(1, c)) End If ' Create a single range from the union of ' categories and selected data, and then ' update the chart.
Sub Create Pivot() Dim obj Table As Pivot Table Dim obj Field As Pivot Field Dim ws As Worksheet Set ws = Worksheets.
Name = "Write-Off Pivot" ' Select the sheet and first cell of the table that contains the data.
refreshes everything, not only the pivot tables but also the ODBC queries.
I have a couple of VBA queries that refer to Data connections and using this option crashes as the command runs the Data connections without the detail supplied from the VBA I recommend the option if you only want the pivots refreshed , that takes a boolean as parameter, on a CTPivot Cache Definition.
excel 9 (2000) and VBA 6 on XP If I open a workbook with pivot tables linked to database and set for 'refresh on open'; i get a 'query refresh' dialog asking if i want to update. I guess i will have to do the following: open workbook loop through worksheets loop through all pivot tales on worksheet refresh pivot table next next close workbook andy other ideas on a quick way to force all to be updated?