Pivot Table Automation
 

I've got a procedure written to automate the creation of a pivot table. I'm
getting a "run-time error 13: type mismatch" error on the last line of the
code snippet below. The code works perfectly on one pivot table, so I copied
the code to use on another pivot table and I get this run time error. Any
ideas?

Dim WSD_s As Worksheet
Dim PTCache_s As PivotCache
Dim PT_s As PivotTable
Dim PRange_s As Range
Dim MyString_s As String

'Insert a new worksheet and name it
Set WSD_s = ActiveWorkbook.Worksheets.Add
WSD_s.Name = "PivotTable"

MyString_s =
"=OFFSET(SITE_COMPARE_DETAIL!$A$1,0,0,COUNTA(SITE_ COMPARE_DETAIL!$A:$A),COUNTA(SITE_COMPARE_DETAIL!$ 1:$1))"
Set PRange_s = Range(MyString_s)

Set PTCache_s = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:=PRange_s)

Pivot Table Automation
 

is MyString_s valid?

in the immediate window you could try
Range(MyString_s ).Select
with the sheets it references as the active sheet.

"Kirk P." wrote:

I've got a procedure written to automate the creation of a pivot table. I'm
getting a "run-time error 13: type mismatch" error on the last line of the
code snippet below. The code works perfectly on one pivot table, so I copied
the code to use on another pivot table and I get this run time error. Any
ideas?

Dim WSD_s As Worksheet
Dim PTCache_s As PivotCache
Dim PT_s As PivotTable
Dim PRange_s As Range
Dim MyString_s As String

'Insert a new worksheet and name it
Set WSD_s = ActiveWorkbook.Worksheets.Add
WSD_s.Name = "PivotTable"

MyString_s =
"=OFFSET(SITE_COMPARE_DETAIL!$A$1,0,0,COUNTA(SITE_ COMPARE_DETAIL!$A:$A),COUNTA(SITE_COMPARE_DETAIL!$ 1:$1))"
Set PRange_s = Range(MyString_s)

Set PTCache_s = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:=PRange_s)