VBA code not working...

B

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
 
T

Tom Ogilvy

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

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
 
B

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")
 
B

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.
 
B

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...
 
T

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).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top