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