Type Mismatch when creating a pivottable macro

[MikeC]

Hi,

I've been searching through this group for similar problems (and found
one) but I was still unable to resolve the issue below. When I try to
set bigcache, it throws a type mismatch at me and I'll be damned if I
can work it out. Any help is much appreciated. My code is:



Function consolidateData()

Dim varArray(3) As Variant
Dim headingNames As New Collection
Dim bigcache As Variant


headingNames.Add "Item1"
headingNames.Add "Item2"
headingNames.Add "Item3"

For i = 2 To Sheets.Count - 1
varArray(i) = Array(Sheets(i). _
Range("A1").CurrentRegion.Address(external:=True,
ReferenceStyle:=xlR1C1), _
headingNames(i - 1))
Next


Set bigcache = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlConsolidation, _
SourceData:=varArray)

bigcache.CreatePivotTable _
TableDestination:="", TableName:="PivotTable3",
DefaultVersion:= _
xlPivotTableVersion13
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems(
_
"Count of Value").Position = 1
ActiveWorkbook.ShowPivotTableFieldList = True
End Function

 

[Iain King]

I've been searching through this group for similar problems (and found
one) but I was still unable to resolve the issue below. When I try to
set bigcache, it throws a type mismatch at me and I'll be damned if I
can work it out. Any help is much appreciated. My code is:

Dim bigcache As Variant

Have you tried making bigcache a PivotCache instead of a Variant?

Iain King