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

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

--
Regards,
Tom Ogilvy


"Brian Easton" wrote in message
...
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

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

In article ,
says...
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

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")
-----Original Message-----
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

In article ,
says...
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

.

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.
-----Original Message-----
What book - Title and page number. You have created
the datatable is was
designed to go against?

--
Regards,
Tom Ogilvy


"Brian Easton"
wrote in message
...
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


.

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

--
Regards,
Tom Ogilvy


"Brian Easton" wrote in message
...
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.
-----Original Message-----
What book - Title and page number. You have created
the datatable is was
designed to go against?

--
Regards,
Tom Ogilvy


"Brian Easton"
wrote in message
...
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


.

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
(TableDestination:=WSD.Range("j2"),
TAbleName:="PivotTable1"

I'm in over my head...
-----Original Message-----
When I fixed the two End With errors, it ran fine for me.

--
Regards,
Tom Ogilvy


"Brian Easton"
wrote in message
...
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.
-----Original Message-----
What book - Title and page number. You have created
the datatable is was
designed to go against?

--
Regards,
Tom Ogilvy


"Brian Easton"
wrote in message
...
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


.



.

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


--
Regards,
Tom Ogilvy

"Brian Easton" wrote in message
...
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
(TableDestination:=WSD.Range("j2"),
TAbleName:="PivotTable1"

I'm in over my head...
-----Original Message-----
When I fixed the two End With errors, it ran fine for me.

--
Regards,
Tom Ogilvy


"Brian Easton"
wrote in message
...
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.
-----Original Message-----
What book - Title and page number. You have created
the datatable is was
designed to go against?

--
Regards,
Tom Ogilvy


"Brian Easton"
wrote in message
...
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


.



.