Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.scripting.vbscript
|
|||
|
|||
Dynamic Array as pivot table SourceData..?
Hi,
As far as I know, the SourceData for a Pivot Table can be an array of ranges, but it's not working for me. The following code creates a dynamic array of ranges, which is then supplied to the SourceData parameter of the PivotTableWizard. This causes an error "Pivot Table Wizard method of Worksheet class failed". ---------------------------------------------------------------------------- ----- im i As Integer Dim m As Integer Dim pivotTableRangeArray() As Range Dim dataSheetNames() As String Dim re As New RegExp ' Populate an array of data sheet names re.IgnoreCase = True re.Pattern = "[a-z]\.[a-z\_]" m = 1 For i = 1 To ActiveWorkbook.Sheets.Count If re.Test(Sheets(i).Name) Then ReDim Preserve dataSheetNames(1 To m) dataSheetNames(m) = Sheets(i).Name 'Debug.Print "Sheet:", i, m, dataSheetNames(m) m = m + 1 End If Next i Set re = Nothing ' Populate an array of ranges for the pivot table For i = 1 To UBound(dataSheetNames) ' populate the pivotTableRangeArray ReDim Preserve pivotTableRangeArray(1 To i) Set pivotTableRangeArray(i) = Sheets(dataSheetNames(i)).Range("A1").CurrentRegio n 'Debug.Print i, UBound(pivotTableRangeArray) Next i ' Create the pivot table - use the With ActiveSheet .PivotTableWizard SourceType:=xlDataBase, _ SourceData:=pivotTableRangeArray, _ TableName:="sdc.vsim_c", _ TableDestination:=.Range("A65536").End(xlUp).Offse t(2, 0) End With ---------------------------------------------------------------------------- ------ Can anyone shed any light on this? Thank you very much, Robert Stober |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic pivot table linked to dynamic excel spreadsheets | Excel Worksheet Functions | |||
Pivot table from dynamic refreshed pivot table | Excel Worksheet Functions | |||
Dynamic sourcedata when copying chart within sheet | Charts and Charting in Excel | |||
Pivot table with a variable range in the sourcedata field | Excel Discussion (Misc queries) | |||
Dynamic table array using Hyperlink | Excel Worksheet Functions |