EasyManuals Logo

Amazon VBA User Manual

Amazon VBA
60 pages
To Next Page IconTo Next Page
To Next Page IconTo Next Page
To Previous Page IconTo Previous Page
To Previous Page IconTo Previous Page
Page #38 background imageLoading...
Page #38 background image
Listing 11.8 Code Used to Create the Top 5 Markets Report
Sub Top5Markets()
‘ Listing 11.8
‘ Produce a report of the top 5 markets
Dim WSD As Worksheet
Dim WSR As Worksheet
Dim WBN As Workbook
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(“R1:AZ1”).EntireColumn.Clear
‘ 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.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:=”Market”, ColumnFields:=”Product”
‘ Set up the data fields
With PT.PivotFields(“Revenue”)
.Orientation = xlDataField
.Function = xlSum
Chapter 11 Using VBA to Create Pivot Tables
268
11
Figure 11.19
The Top 5 Markets report
contains two pivot
tables.
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 268

Questions and Answers:

Question and Answer IconNeed help?

Do you have a question about the Amazon VBA and is the answer not in the manual?

Amazon VBA Specifications

General IconGeneral
BrandAmazon
ModelVBA
CategorySoftware
LanguageEnglish

Related product manuals