VBA Pivot Table Help

jvanbonn

Board Regular
Joined
Mar 11, 2011
Messages
71
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.:biggrin:

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:
Code:
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?":confused:
answers from livingroom... "On the shelf right in front of YOU!":p

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!
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

AnAnalyst

Well-known Member
Joined
Jul 10, 2007
Messages
536
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

Board Regular
Joined
Mar 11, 2011
Messages
71
Woo Hoo!:biggrin:

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,042
Messages
5,599,469
Members
414,312
Latest member
mikefire911

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top