To Anyone who can help,
In the book: "Pivot Table Data Crunching for Microsoft Excel 2007", I entered the VBA macro listings 11.1 and 11.2 (pages 242 to 246). I don't have Excel 2007, but the macro code looks the same as Excel 2003 macro code. I created a sample database with similar fields. In those cases where the field names changed, I also made a corresponding change in the VBA code that made references to these field names. I can manually create the Pivot Table without using the code (not really the point here, but this proves to me that I know how to do it manually). I made sure to place the data for the pivot table in the worksheet named "PivotTable" so the code will work with it. When I try to run either macro, I get the following error in the Excel VBA:
Run time error '1004'
This command requires at least two rows of source data. You cannot use the command on a selection in only one row. Try the following:
* If you're using an advanced filter, select a range of cells that contains at least two rows of data. Then click the Advanced Filter command again.
* If you're creating a PivotTable report or PivotChart report, type a [it cuts off here]
When I click on Debug, it goes to the following line of code for both macros:
' Create the Pivot Table from the Pivot Cache
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
Cells(2, FinalCol + 2), TableName:="PivotTable1")
My worksheet data are defined as follows:
3 fields:
Business Segment
Region
Sales Revenue
Business Segment sub elements:
Computer Hardware
Housekeeping and Organization
Landscaping and Area Beautification
Maintenance and Repair
Toy Repair
Region sub elements:
MidWest
North
South
West
Sales Revenue:
All numeric data, no blanks or zeros
My VBA macro code is as follows:
Sub CreatePivot()
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Dim FinalCol As Long
Set WSD = Worksheets("PivotTable")
' 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(Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Columns.Count). _
End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=PRange)
' Create the Pivot Table from the Pivot Cache
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
Cells(2, FinalCol + 2), TableName:="PivotTable1")
' Turn off updating while building the table
PT.ManualUpdate = True
' Set up the row & column fields
PT.AddFields RowFields:=Array("Business Segment", "Sales Revenue"), _
ColumnFields:="Region"
' Set up the data fields
With PT.PivotFields("Sales Revenue")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
End With
' Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True
' Format the pivot table
PT.ShowTableStyleRowStripes = True
PT.TableStyle2 = "PivotStyleMedium10"
End Sub
Sub CreateSummaryReportUsingPivot()
' Use a Pivot Table to create a static summary report
' with model going down the rows and regions across
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Set WSD = Worksheets("PivotTable")
' Delete any prior pivot tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT
WSD.Range("H1:AZ1").EntireColumn.Clear
' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Application.Columns.Count). _
End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=PRange.Address)
' Create the Pivot Table from the Pivot Cache
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
Cells(2, FinalCol + 2), TableName:="PivotTable1")
' Turn off updating while building the table
PT.ManualUpdate = True
' Set up the row fields
PT.AddFields RowFields:="Business Segment", ColumnFields:="Region"
' Set up the data fields
With PT.PivotFields("Sales Revenue")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
End With
With PT
.ColumnGrand = False
.RowGrand = False
.NullString = "0"
End With
' Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True
' PT.TableRange2 contains the results. Move these to R10
' as just values and not a real pivot table.
PT.TableRange2.Offset(1, 0).Copy
WSD.Cells(8 + PT.TableRange2.Rows.Count, FinalCol + 2). _
PasteSpecial xlPasteValues
' At this point, the worksheet looks like Figure 11.6
' Delete the original Pivot Table & the Pivot Cache
PT.TableRange2.Clear
Set PTCache = Nothing
WSD.Activate
Range("A1").Select
End Sub
What am I doing wrong? Thank you!!
Bruce
