VBA Pivot Table Help

[jvanbonn]

First, I want to thank MrExcel for such a great resource. I've learned a great deal and come a long way in just the past few days.

My dilemma:

I'm attempting a VBA Code to create a pivot table. I have the data, with field names in row 1, on a worksheet named "temp". The VBA Code should create a pivot table on a new sheet named "Pivot"

Here is my latest version of the code so far:

Function SheetExists(SheetName As String) As Boolean
' returns TRUE if the sheet exists in the active workbook
    SheetExists = False
    On Error GoTo NoSuchSheet
    If Len(Sheets(SheetName).Name) > 0 Then
        SheetExists = True
        Exit Function
    End If
NoSuchSheet:
End Function

Sub NewPivotTable()
'
' NewPivotTable Macro
'
Dim PTSheet As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Variant

'Delete old "Pivot" worksheet
If SheetExists("Pivot") Then
    Application.DisplayAlerts = False
    Sheets("Pivot").Delete
    Application.DisplayAlerts = True
End If

Set PTSheet = Worksheets.Add
    PTSheet.Name = "Pivot"

Set PRange = Worksheets("temp").Range("A1:M147") '147 Rows & 13 Columns w/ headers in row 1

Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
    SourceData:="'" & PTSheet.Name & "'!" & PRange.Address(, , xlR1C1))
       
Set PT = PTCache.CreatePivotTable(TableDestination:="'Pivot'!R3C1", _
            TableName:="PivotTable1")

When I run the code, I get a Run-time error '1004' when Excel attemps to SET PT=...

Run-time error '1004':

The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable Field, you must type a new name for the field.

This really has me stumped.

Is this just like...
opens the fridge door..."honey where's the ketchup?"
answers from livingroom... "On the shelf right in front of YOU!"

When a PivotCache is created, does Excel import row 1 as the field names?

Thanks so much for all guidance, suggestions, and assistance!

Have a great weekend!

 

[AnAnalyst]

Hi there,

Seems to me you're trying to create the pivotcache from the wrong source:

Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:="'" & PTSheet.Name & "'!" & PRange.Address(, , xlR1C1))

Shouldn't this be:

Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:="'temp'!" & PRange.Address(, , xlR1C1))

 

[jvanbonn]

Woo Hoo!

Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
    SourceData:="'" & [SIZE=3][B][COLOR=Red]PTSheet[/COLOR][/B][/SIZE].name & "'!" & PRange.Address(, , xlR1C1), _
    Version:=xlPivotTableVersion12)

I was pointing to the wrong sheet! I needed to point to the "temp" worksheet.