VBA code not working...

[Brian Easton]

Can someone take a look at this code and tell me why it's
not working. I copied it from a book (probably not a
good first step) and I can't figuer out what the hang up
is.

Sub CreatePivot()
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Set WSD = Worksheets("Pivot Table")

' Delete any prior pivot tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT

' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(65536, 1).End(xlUp).Row
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, 8)
Set PTCache = ActiveWorkbook.PivotCaches.Add
(SourceType:=xlDatabase, SourceData:=PRange.Address)
Set PT = PTCache.CreatePivotTable
(TableDestination:=WSD.Range("j2"),
TAbleName:="PivotTable1")
PT.ManualUpdate = True
' Set up the row & Colum fields
PT.AddFields RowFields:=Array("Month", "ItemNumber")
With PT.PivotFields("QuantityOnPurchaseOrder")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
With PT.PivotFields("QuantityOnSalesOrder")
.Orientation = xlDataField
.Function = xlSum
.Position = 2
With PT.PivotFields("QuantityInvoice")
.Orientation = xlDataField
.Function = xlSum
.Position = 3
End With

' Calc the Pivot Table
PT.ManualUpdate = False
PT.ManualUpdate = True
End Sub

 

[Tom Ogilvy]

What book - Title and page number. You have created the datatable is was
designed to go against?

 

[Tushar Mehta]

How is it "not working?"

Does it compile? If not, what error message does the VBE display?

If it runs, does it fault? If so, what line does it stop on? What is
the error message?

If not, how does the result not match your expectation?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Brian Easton]

This is the line it is hanging up on.
It was originally stopping due to a lack of an "end with"
statment, because I added a few "with" statments, but I
fixed that.

the error is a run time "1004"

Set PT = PTCache.CreatePivotTable
(TableDestination:=WSD.Range("j2"),
TAbleName:="PivotTable1")

 

[Brian Easton]

The name of the workbook is Pivot table, the database is
located in worksheet #1. There are 6
columns "Item", "Date"(not used), "Month", QtyonPO,
QtyOnSO, and InvoiceQty.

 

[Tom Ogilvy]

When I fixed the two End With errors, it ran fine for me.

 

[Brian Easton]

it hangs up on the "Set WSD = Worksheets("Pivot Table")"
line when I don't have a worksheet named pivot table.
when I name a sheet this then it hangs on "Set PT =
PTCache.CreatePivotTable
I'm in over my head...

 

[Tom Ogilvy]

Well, you have to have a database on the worksheet Pivot table that matches
the settings in your code. Of course if you don't have a sheet named Pivot
Table, you can't set a reference to it.

It sounds like you need to go back and re-read some of the earlier chapters
in the book (which you never told us what it is as requested).