VBA Pivot Table Code Help

J

jvanbonn

Board Regular
Joined
Mar 11, 2011
Messages
71
I don't understand how to make this code work. Any help would be appreciated.

I can create a Pivot Table with the wizard just fine, but with the following code...
Code: 
Sub NewPivotTable()
'
' NewPivotTable Macro
'
Dim PTSheet As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Variant

    Sheets.Add
    ActiveSheet.name = "Pivot"
    
Set PTSheet = Worksheets("Pivot")
Set PRange = Sheets("temp").Range("PivotData")
' MsgBox PRange.Address
Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
    SourceData:="'" & PTSheet.name & "'!" & PRange.Address(, , xlR1C1), _
    Version:=xlPivotTableVersion12)
Set PT = PTCache.CreatePivotTable(TableDestination:="'Pivot'!R3C1", _
    TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12)
End Sub
I just get the following error when Excel tries to SET PT = ....

Error said:
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.
Click to expand...
I'm using Excel 2007 on WinXP
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
As I peruse these threads, I realize there are some brilliant programmers here, which are way out of my league.

I'm still stumped with making this work; if anyone could help, I'd appreciate it.

Thank you!
 
Upvote 0
I realize I'm talking to myself here at the moment...

The following code reads the data into the PivotCache, but does not identify ROW 1 as the labels. It just brings in the data.
Code: 
Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
    SourceData:="'" & PTSheet.name & "'!" & PRange.Address(, , xlR1C1), _
    Version:=xlPivotTableVersion12)
Therefore the code to SET the PivotTable (PT) fails.

What do I need to change to properly SET a PTCache As PivotCache WITH the labels?
:confused:
 
Upvote 0
Re: VBA Pivot Table Code Help - SAMPLE DATA

<table style="width: 746px; height: 219px;" border="0" cellpadding="0" cellspacing="0"><tbody><tr height="15"><td valign="top" width="61" height="15">Co
</td><td valign="top" width="61" height="15">Rte
</td><td valign="top" width="61" height="15">PM1
</td><td valign="top" width="84" height="15">Number
</td><td valign="top" width="61" height="15">Name
</td><td valign="top" width="107" height="15">Description
</td><td valign="top" width="61" height="15">Work_By
</td><td valign="top" width="61" height="15">Action
</td><td valign="top" width="61" height="15">Estimate
</td><td style="vertical-align: top;">
</td><td valign="top" width="61" height="15">EA
</td><td valign="top" width="61" height="15">Rank
</td><td valign="top" width="61" height="15">Area
</td></tr><tr height="15"><td valign="top" width="61" height="15">Hum
</td><td valign="top" width="61" height="15">1
</td><td valign="top" width="61" height="15">18.5
</td><td valign="top" width="84" height="15">11 0315
</td><td valign="top" width="61" height="15">GARCIA RIVER
</td><td valign="top" width="107" height="15">Steel truss members strengthening.
</td><td valign="top" width="61" height="15">5-STRAIN
</td><td valign="top" width="61" height="15">Seismic-Retrofit
</td><td valign="top" width="61" height="15">413000
</td><td style="vertical-align: top;">
</td><td valign="top" width="61" height="15">0AA70
</td><td valign="top" width="61" height="15">1.65
</td><td valign="top" width="61" height="15">01A - Bubba Smith
</td></tr><tr height="15"><td valign="top" width="61" height="15">Hum
</td><td valign="top" width="61" height="15">1
</td><td valign="top" width="61" height="15">104.96
</td><td valign="top" width="84" height="15">11 0349
</td><td valign="top" width="61" height="15">SOUTH FORK EEL RIVER
</td><td valign="top" width="107" height="15">Steel truss member strengthening.
</td><td valign="top" width="61" height="15">5-STRAIN
</td><td valign="top" width="61" height="15">Seismic-Retrofit
</td><td valign="top" width="61" height="15">487000
</td><td style="vertical-align: top;">
</td><td valign="top" width="61" height="15">0AA71
</td><td valign="top" width="61" height="15">2.31
</td><td valign="top" width="61" height="15">01A - Bubba Smith
</td></tr><tr height="15"><td valign="top" width="61" height="15">Hum
</td><td valign="top" width="61" height="15">1
</td><td valign="top" width="61" height="15">104.96
</td><td valign="top" width="84" height="15">11 0349
</td><td valign="top" width="61" height="15">SOUTH FORK EEL RIVER
</td><td valign="top" width="107" height="15">F1-10 / F2-0 / F3-0 / Rail Type-WOOD.
</td><td valign="top" width="61" height="15">5-STRAIN
</td><td valign="top" width="61" height="15">Railing-Upgrade
</td><td valign="top" width="61" height="15">396880
</td><td style="vertical-align: top;">
</td><td valign="top" width="61" height="15">0AA72
</td><td valign="top" width="61" height="15">1.26
</td><td valign="top" width="61" height="15">01A - Bubba Smith
</td></tr><tr height="15"><td valign="top" width="61" height="15">Hum
</td><td valign="top" width="61" height="15">1
</td><td valign="top" width="61" height="15">18.5
</td><td valign="top" width="84" height="15">11 0313
</td><td valign="top" width="61" height="15">GARCIA RIVER
</td><td valign="top" width="107" height="15">F1-10 / F2-0 / F3-0 / Rail Type-WOOD.
</td><td valign="top" width="61" height="15">5-STRAIN
</td><td valign="top" width="61" height="15">Railing-Upgrade
</td><td valign="top" width="61" height="15">359160
</td><td style="vertical-align: top;">
</td><td valign="top" width="61" height="15">0AA73
</td><td valign="top" width="61" height="15">1.32
</td><td valign="top" width="61" height="15">01A - Bubba Smith
</td></tr></tbody></table><table style="width: 556px; height: 108px;" border="0" cellpadding="0" cellspacing="0"><tbody><tr height="15"><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="84" height="15">
</td><td valign="top" width="61" height="15">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td valign="top" width="107" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td></tr><tr height="15"><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="84" height="15">
</td><td valign="top" width="61" height="15">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td valign="top" width="107" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td></tr><tr height="15"><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="84" height="15">
</td><td valign="top" width="61" height="15">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td valign="top" width="107" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td></tr><tr height="15"><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="84" height="15">
</td><td valign="top" width="61" height="15">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td valign="top" width="107" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td></tr><tr height="15"><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="84" height="15">
</td><td valign="top" width="61" height="15">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td valign="top" width="107" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td><td valign="top" width="61" height="15">
</td></tr></tbody></table>
 
Upvote 0
Woo Hoo!:biggrin:

Code: 
Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
    SourceData:="'" & [SIZE=3][COLOR=Red]PTSheet[/COLOR][/SIZE].name & "'!" & PRange.Address(, , xlR1C1), _
    Version:=xlPivotTableVersion12)
I was pointing to the wrong sheet! I needed to point to the "temp" worksheet.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
VBA/Macro: Adding Pivot table with connection string
Replies
1
Views
252
Jette3173
J
M
  • Question Question
VBA to match number of table rows to pivot rows?
Replies
6
Views
96
Alex Blakenburg
A
D
  • Question Question
VBA Coding for adding Pivot Table with changing data table
Replies
6
Views
244
darmst24
D
ghull99
  • Question Question
Attempting VBA to refresh "Chart Title" based on Pivot Filters. Filter1 works in VBA, but title doesn't update based on filter2.
Replies
0
Views
73
ghull99
ghull99
I
  • Question Question
Error "Unable to set the Name property of the PivotTable class"
Replies
1
Views
275
RoryA
RoryA

Forum statistics

Threads
1,224,586
Messages
6,179,719
Members
452,939
Latest member
WCrawford

Share this page

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
Back
Top