Amazon VBA Manual Download Page 41

Page: 41 / 60

271

Using Advanced Pivot Table Techniques

‘ 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, Fi 2), TableName:=”PivotTable1”)

‘ Turn off updating while building the table
PT.ManualUpdate = True

‘ Set up the row fields
PT.AddFields RowFields:=”Customer”, ColumnFields:=”Data”

‘ Set up the data fields
With PT.PivotFields(“Revenue”)

.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = “#,##0”
.Name = “Total Revenue”

End With

‘ Sort Stores descending by sum of revenue
PT.PivotFields(“Customer”).AutoSort Order:=xlDescending, _

Field:=”Total Revenue”

‘ Show only the top 3 stores
PT.PivotFields(“Customer”).AutoShow Type:=xlAutomatic, Range:=xlTop, _

Count:=3, Field:=”Total Revenue”

‘ Ensure that we get zeros instead of blanks in the data area
PT.NullString = “0”

‘ Calc the pivot table to allow the date label to be drawn
PT.ManualUpdate = False
PT.ManualUpdate = True

‘ Produce summary reports for each customer
For i = 1 To 3

PT.TableRange2.Offset(i + 1, 1).Resize(1, 1).ShowDetail = True
‘ The active sheet has changed to the new detail report
‘ Add a title
Range(“A1:A2”).EntireRow.Insert
Range(“A1”).Value = “Detail for “ & _

PT.TableRange2.Offset(i + 1, 0).Resize(1, 1).Value & _
“ (Customer Rank: “ & i & “)”

Next i

continues

12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 271

Summary of Contents for VBA

Page 1: ... need to enable macros in the Trust Center From the Office icon menu choose Excel Options Trust Center Trust Center Settings Macro Settings Choose one of the options below Disable all macros with notification this set ting is equivalent to medium macro security in Excel 2003 When you open a workbook that contains macros a message will appear alerting that there are macros in the workbook If you ex...

Page 2: ...ions Popular Then choose Show Developer Tab in the Ribbon As shown in Figure 11 1 the Code group on the Developer tab of the Ribbon offers icons for accessing the Visual Basic Editor Macros dialog box macro recording tools and Macro Security setting Visual Basic Editor From Excel press Alt F11 or choose Developer Code Visual Basic to open the Visual Basic Editor as shown in Figure 11 2 The three m...

Page 3: ...l Basic is a powerful development environment Although this chapter cannot offer a complete course on VBA if you are new to VBA you should take advantage of these impor tant tools As you begin to type code Excel may offer a drop down with valid choices This fea ture known as AutoComplete allows you to type code faster and eliminate typing mis takes For assistance on any keyword put the cursor in t...

Page 4: ... would be a lot better if Excel could record selecting cells using the End key This is one of the shortcomings of the macro recorder In reality Excel pros use the macro recorder to record code but then expect to have to clean up the recorded code Understanding Object Oriented Code VBA is an object oriented language Most lines of VBA code follow the Noun Verb syntax However in VBA it is called Obje...

Page 5: ... the last row with data The equivalent of doing this in VBA is to use the following code Range A1048576 End xlUp Select You don t need to select this cell you just need to find the row number that contains the last row The following code locates this row and saves the row number to a variable named FinalRow FinalRow Range A1048576 End xlUp Row There is nothing magic about the variable name FinalRo...

Page 6: ... information about a cell or about any object A statement to create a typical variable such as x Range A1 assigns the current value of A1 to the variable x However you can use the Set keyword to create an object variable Set x Range A1 You ve now created a super variable that contains all the properties of the cell Instead of having a variable with only one value you have a variable in which you c...

Page 7: ...s that are new in Excel 2007 If you record a macro that uses these methods you cannot share the macro with someone using Excel 2003 or earlier Table 11 1 Methods New in Excel 2007 Method Description ClearAllFilters Clears all filters in the pivot table ClearTable Removes all fields from the pivot table but keeps the pivot table intact ConvertToFormulas Converts a pivot table to cube formulas This ...

Page 8: ...ToolTips FieldListSortAscending Controls the sort order of fields in the PivotTable Field List When this property is True the fields are sorted in alphabetical order When it is set to False the fields are presented in the same sequence as the data source columns InGridDropZones Controls whether you can drag and drop fields onto the grid Changing the pivot table layout also changes this property Ch...

Page 9: ...ptimize performance for fields with many items and allows you to avoid using custom list based sorting TableStyle2 Specifies the pivot table style currently applied to the pivot table Note that previous versions of Excel offered a weak AutoFormat option That feature s settings were held in the TableStyle property so Microsoft had to use TableStyle2 as the property name for the new pivot table styl...

Page 10: ...ing this line of code you have a strange looking blank pivot table like the one shown in Figure 11 3 You now have to use code to drop fields onto the table If you choose the Defer Layout Update setting in the user interface to build the pivot table Excel does not recalculate the pivot table after you drop each field onto the table By default in VBA Excel calculates the pivot table as you execute e...

Page 11: ...report with revenue it assumes you want to sum the rev enue But there is a problem Say that one of the revenue cells is blank When you build the pivot table even though 99 9 of fields are numeric Excel assumes you have alphanumeric data and offers to count this field This is annoying It seems to be an anomaly that on one hand you are expected to make sure that 100 of your cells have numeric data b...

Page 12: ...a medium table style Format the pivot table PT ShowTableStyleRowStripes True PT TableStyle2 PivotStyleMedium10 At this point you have a complete pivot table like the one shown in Figure 11 4 Chapter 11 UsingVBA to Create PivotTables 242 11 Figure 11 4 Fewer than 50 lines of code create this pivot table in less than a sec ond Listing 11 1 shows the complete code used to generate the pivot table Lis...

Page 13: ...on 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 Learning Why You Cannot Move or Change Part of a Pivot Report Although pivot tables are incredible they have annoying limitations You cannot move or change just part of a pivot table For example try to run a m...

Page 14: ...ur pivot table you might need to use an offset of two or more rows to get rid of extrane ous information at the top of the pivot table The code copies PT TableRange2 and uses PasteSpecial on a cell five rows below the cur rent pivot table At that point in the code your worksheet appears as shown in Figure 11 6 The table in R2 is a live pivot table and the table in R10 is just the copied results Ch...

Page 15: ...votTable 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 Revenue Orientation xlDataField Function xlSum Position 1 End With With PT ColumnGrand False RowGrand False NullString 0 End With Calc the...

Page 16: ...cross the columns A sample report is shown in Figure 11 7 Chapter 11 UsingVBA to Create PivotTables 246 11 Figure 11 7 A typical request is to take transactional data and produce a summary by product for product line managers You can use a pivot table to get 90 of this report and then a little formatting to finish it Listing 11 2 Continued The key to producing this data quickly is to use a pivot t...

Page 17: ...el experts rely on being able to ride the range using the End and arrow keys Blank cells ruin this ability The title is boring Most people would agree that Sum of Revenue is an annoying title Some captions are extraneous Invoice Date floating in cell T2 of Figure 11 8 really does not belong in a report The default alphabetical sort order is rarely useful Product line managers are going to want the...

Page 18: ... zero in the empty cells N O T E Because of the page break problem you may find it is easier to do away with the pivot table s subtotal rows and have the Subtotal method add subtotal rows with page breaks You need a way to turn off the pivot table subtotal rows offered for Product in Figure 11 8 These rows show up automatically whenever you have two or more row fields If you had four row fields yo...

Page 19: ...To set up a format in tenths of millions that uses the 0 0 MM format you would use this line of code PT PivotFields Sum of Revenue NumberFormat 0 0 M Here the format is quotation mark pound comma pound pound zero period zero comma comma quotation mark quotation mark M quotation mark quotation mark quo tation mark The three quotation marks at the end are correct You use two quotation marks to simul...

Page 20: ...dditional Annoyances When Creating Your Final Report You ve reached the end of the adjustments that you can make to the pivot table To achieve the final report you have to make the remaining adjustments after converting the pivot table to regular data Figure 11 9 shows the pivot table with all the adjustments described in the preceding sec tions and with PT TableRange2 selected Chapter 11 UsingVBA...

Page 21: ...ats to copy the data to the report sheet 11 In Excel 2000 and earlier xlPasteValuesAndNumberFormats was not available You had to use Paste Special twice once as xlPasteValues and once as xlPasteFormats C A U T I O N In the current example the TableRange2 property includes only one row to eliminate row 2 as shown in Figure 11 9 If you had a more complex pivot table with several column fields and or...

Page 22: ...nterface you would type an equal sign press the up arrow key and then press Ctrl Enter 4 Reselect all the cells in column A that make up the report This step is necessary because the Paste Special step cannot work with noncontiguous selections 5 Copy the formulas in column A and convert them to values by choosing Home Clipboard Paste Paste Values Fixing the Outline view in VBA requires fewer steps...

Page 23: ...e that you would always have three years and a total the code to add subto tals for each Line of Business group would be the following Add Subtotals by Product Be sure to add a page break at each change in product Selection Subtotal GroupBy 1 Function xlSum TotalList Array 3 4 5 6 _ PageBreaks True 11 Figure 11 10 Use automatic subtotals because doing so enables you to add a page break after each ...

Page 24: ...fore Cells GrandRow 1 Putting It All Together Listing 11 3 produces the product line manager reports in a few seconds Listing 11 3 Code That Produces the Product Line Report in Figure 11 11 Sub ProductLineReport Product and Market as Row Years as Column Dim WSD As Worksheet Dim PTCache As PivotCache Dim PT As PivotTable Dim PRange As Range Dim FinalRow As Long Dim TotColumns Set WSD Worksheets Piv...

Page 25: ... End With Calc the pivot table PT ManualUpdate False PT ManualUpdate True Group by Year WSD Activate Cells 3 FinalCol 4 Group Start True End True _ Periods Array False False False False False False True Replace blanks with zero PT NullString 0 Remove subtotals by product PT PivotFields Product Subtotals 1 True PT PivotFields Product Subtotals 1 False PT ColumnGrand False Sort descending by revenue...

Page 26: ...ontalAlignment xlRight Range A3 B3 HorizontalAlignment xlLeft Repeat rows 1 3 at the top of each page WSR PageSetup PrintTitleRows 1 3 Add subtotals FinalCol Cells 3 Columns Count End xlToLeft Column ReDim Preserve TotColumns 1 To FinalCol 2 For i 3 To FinalCol TotColumns i 2 i Next i Selection Subtotal GroupBy 1 Function xlSum _ TotalList TotColumns Replace True _ PageBreaks True SummaryBelowData...

Page 27: ...t with the data field as the inner most column field It is often preferable to have the data field as the outermost row field When a pivot table is going to have more than one data field you have a virtual field named Σ Values in the drop zones of the PivotTable Field List In VBA this equivalent virtual field is named Data Where you place the data field in the AddFields method determines which vie...

Page 28: ...le report has multiple data fields as the innermost column field Figure 11 13 By moving the data field to the first row field you can obtain this view of the multiple data fields Calculated Data Fields Pivot tables offer two types of formulas The most useful type defines a formula for a calcu lated field This adds a new field to the pivot table Calculations for calculated fields are ...

Page 29: ... as Avg Price Note that this name must be different from the name for the calculated field Listing 11 4 produces the report shown in Figure 11 14 Listing 11 4 Code That Calculates an Average Price Field as a Second Data Field Sub TwoDataFields Listing 11 4 Dim WSD As Worksheet Dim PTCache As PivotCache Dim PT As PivotTable Dim PRange As Range Dim FinalRow As Long Set WSD Worksheets PivotTable Dim ...

Page 30: ...eld Function xlSum Position 3 NumberFormat 0 00 Name Avg Price End With Ensure that we get zeros instead of blanks in the data area PT NullString 0 Calc the pivot table PT ManualUpdate False PT ManualUpdate True WSD Activate Range R1 Select End Sub Calculated Items Say that in your company one manager is responsible for Landscaping Grounds Care and Green Plants and Foliage Care The idea behind a c...

Page 31: ...t continues Listing 11 5 Code That Adds a New Item Along the Product Dimension Sub CalcItemsProblem Listing 11 5 Dim WSD As Worksheet Dim PTCache As PivotCache Dim PT As PivotTable Dim PRange As Range Dim FinalRow As Long Set WSD Worksheets PivotTable Dim WSR As Worksheet Delete any prior pivot tables For Each PT In WSD PivotTables PT TableRange2 Clear Next PT WSD Range R1 AZ1 EntireColumn Clear ...

Page 32: ...long the product dimension PT PivotFields Product CalculatedItems _ Add Plants Group _ Landscaping Grounds Care Green Plants and Foliage Care Resequence so that the report Landscaping First PT PivotFields Product _ PivotItems Landscaping Grounds Care Position 1 PT PivotFields Product _ PivotItems Green Plants and Foliage Care Position 2 PT PivotFields Product _ PivotItems Plants Group Position 3 S...

Page 33: ...only plausible method for dealing with this sit uation is to attempt to hide the products that make up the Plants Group With PT PivotFields Product PivotItems Landscaping Grounds Care Visible False PivotItems Green Plants and Foliage Care Visible False End With The results are shown in Figure 11 16 Figure 11 15 Unless you love restating numbers to the Securities and Exchange Commission avoid using...

Page 34: ...arter and month Listing 11 6 Code That Uses the Group Feature to Roll Daily Dates Up to Monthly Dates Sub ReportByMonth Listing 11 6 Dim WSD As Worksheet Dim PTCache As PivotCache Dim PT As PivotTable Dim PRange As Range Dim FinalRow As Long Set WSD Worksheets PivotTable Dim WSR As Worksheet Delete any prior pivot tables For Each PT In WSD PivotTables PT TableRange2 Clear Next PT WSD Range R1 AZ1 ...

Page 35: ...lUpdate False PT ManualUpdate True WSD Activate Range R1 Select End Sub Group by Week You probably noticed that Excel allows you to group by day month quarter and year There is no standard grouping for week You can however define a group that bunches groups of seven days By default Excel starts the week based on the first date found in the data This means that the default week would run from Thurs...

Page 36: ... 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 t...

Page 37: ...advanced tech niques available with pivot tables The following sections discuss such techniques Using AutoShow to Produce Executive Overviews If you are designing an executive dashboard utility you might want to spotlight the top five markets As with the AutoSort option you could be a pivot table pro and never have stumbled across the AutoShow feature in Excel This setting lets you select either t...

Page 38: ...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 ta...

Page 39: ...ble data to row 3 of the report sheet Use offset to eliminate the title row of the pivot table PT TableRange2 Offset 1 0 Copy WSR A3 PasteSpecial Paste xlPasteValuesAndNumberFormats LastRow WSR Cells 65536 1 End xlUp Row WSR Cells LastRow 1 Value Top 5 Total Go back to the pivot table to get totals without the AutoShow PT PivotFields Market Orientation xlHidden PT ManualUpdate False PT ManualUpdat...

Page 40: ...l the records that make up that cell PT TableRange2 Offset 2 1 Resize 1 1 ShowDetail True Listing 11 9 produces a pivot table with the total revenue for the top three stores and ShowDetail for each of those stores This is an alternative method to using the Advanced Filter report The results of this macro are three new sheets Figure 11 20 shows the first sheet created Chapter 11 UsingVBA to Create ...

Page 41: ...lds Customer ColumnFields Data Set up the data fields With PT PivotFields Revenue Orientation xlDataField Function xlSum Position 1 NumberFormat 0 Name Total Revenue End With Sort Stores descending by sum of revenue PT PivotFields Customer AutoSort Order xlDescending _ Field Total Revenue Show only the top 3 stores PT PivotFields Customer AutoShow Type xlAutomatic Range xlTop _ Count 3 Field Total...

Page 42: ...perty and display the results of the user form Another interesting use is to loop through all PivotItems and display them one at a time in the page field You can quickly produce top 10 reports for each region using this method To determine how many regions are available in the data use PT PivotFields Region PivotItems Count Either of these loops would work For i 1 To PT PivotFields Region PivotIte...

Page 43: ... Creates a New Workbook per Region Sub Top5ByRegionReport Listing 11 10 Produce a report of top 5 customers for each region 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 Ra...

Page 44: ...ring 0 Calc the pivot table PT ManualUpdate False PT ManualUpdate True Ctr 0 Loop through each region For Each PivItem In PT PivotFields Region PivotItems Ctr Ctr 1 PT PivotFields Region CurrentPage PivItem Name PT ManualUpdate False PT ManualUpdate True Create a new blank workbook with one worksheet Set WBN Workbooks Add xlWBATWorksheet Set WSR WBN Worksheets 1 WSR Name PivItem Name Set up Title ...

Page 45: ... store is PT PivotFields Store PivotItems Minneapolis Visible False You need to be very careful never to set all items to False otherwise the macro ends with an error This tends to happen more than you would expect An application may first show products A and B and then on the next loop show products C and D If you attempt to make A and B not visible before making C and D visible no products will ...

Page 46: ... PivotItem This is somewhat dangerous because you don t know whether the underlying fields will have data for South on any given day Be sure to set error checking to resume in case South doesn t exist today On Error Resume Next PT PivotFields Region PivotItems South Position 1 On Error GoTo 0 Using Sum Average Count Min Max and More So far every example in this chapter has involved summing data It...

Page 47: ...the total a percentage of the row a percentage of the column or as the percent difference from the previous or next item All these settings are controlled through the Calculation property of the page field The valid properties for Calculation are xlPercentOf xlPercentOfColumn xlPercentOfRow xlPercentOfTotal xlRunningTotal xlPercentDifferenceFrom xlDifferenceFrom xlIndex and xlNoAdditionalCalculati...

Page 48: ...to low and to see their sizes in relation to each other Percentage of a Specific Item You can use the xlPercentDifferenceFrom setting to express revenues as a percentage of the California market sales Show revenue as a percentage of California With PT PivotFields Revenue Orientation xlDataField Function xlSum Caption of California Calculation xlPercentDifferenceFrom BaseField Market BaseItem Calif...

Page 49: ...tures are available in the user interface nor in VBA Similarly if you use any of these features the code runs only in Excel 2007 There is no hope of going backward to share the code with someone using Excel 2003 Using the New Filters In previous versions of Excel the filtering feature allowed you to choose one or more pivot items from a drop down list The only conceptual filter was the top 10 Auto...

Page 50: ...r exam ple to find all the branches where the total revenue is over 100 000 you would use this code PT PivotFields Branch PivotFilters Add _ Type xlValueIsGreaterThan _ DataField PT PivotFields Sum of Revenue _ Value1 100000 Other value filters might allow you to specify that you want branches where the revenue is between 50 000 and 100 000 In this case you would specify one limit as Value1 and th...

Page 51: ... dates in May xlAllDatesInPeriodJune Filters for all dates in June xlAllDatesInPeriodJuly Filters for all dates in July xlAllDatesInPeriodAugust Filters for all dates in August xlAllDatesInPeriodSeptember Filters for all dates in September xlAllDatesInPeriodOctober Filters for all dates in October xlAllDatesInPeriodNovember Filters for all dates in November xlAllDatesInPeriodDecember Filters for a...

Page 52: ...rs for all captions that are greater than the specified value xlCaptionIsGreaterThanOrEqualTo Filters for all captions that are greater than or match the specified value xlCaptionIsLessThan Filters for all captions that are less than the specified value xlCaptionIsLessThanOrEqualTo Filters for all captions that are less than or match the specified value xlCaptionIsNotBetween Filters for all captio...

Page 53: ...ious day xlNotSpecificDate Filters for all dates that do not match a specified date xlSpecificDate Filters for all dates that match a specified date xlTopCount Filters for the specified number of values from the top of a list xlTopPercent Filters for the specified percentage of values from a list xlTopSum Sums the values from the top of the list xlValueDoesNotEqual Filters for all values that do n...

Page 54: ...as four check boxes that modify the styles in the PivotTable Styles Gallery Chapter 11 UsingVBA to Create PivotTables 284 11 Figure 11 24 The four check boxes and gallery of styles offer many variations for for matting the pivot table Table 11 3 Continued Filter Type Description The following four lines of code are equivalent to turning on all four settings in the PivotTable Style Options group PT...

Page 55: ...ated in the same Excel session For this reason you should always explicitly choose the layout that you want Use the RowAxisLayout method valid values are xlTabularRow xlOutlineRow or xlCompactRow 11 Previous versions of Excel offered an AutoFormat feature for pivot tables This feature was annoying because it actually changed the layout of your pivot table That obsolete command used the TableStyle ...

Page 56: ...total rows from the visualization If you have 30 branches that average 50 000 in revenue each the total for the 30 branches is 1 5 million If you include the total in the data visualization the total gets the largest bar and all the branch records have tiny bars In the Excel user interface you always want to use the Add Rule or Edit Rule choice to choose the option All Cells Showing Sum of Revenue...

Page 57: ...e building the table PT ManualUpdate True Set up the row column fields PT AddFields RowFields Branch _ ColumnFields Data Set up the data fields With PT PivotFields Revenue Orientation xlDataField Function xlSum Position 1 End With Calc the pivot table PT ManualUpdate False PT ManualUpdate True Apply a Databar PT TableRange2 Cells 3 2 Select Selection FormatConditions AddDatabar Selection FormatCon...

Page 58: ...ableWizard method to create the table Sub PivotExcel97Compatible Pivot Table Code for Excel 97 Users Dim WSD As Worksheet 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 Define input area FinalRow WSD Cells Rows Count 1 End xlUp Row FinalCol WSD Cells 1 Columns Coun...

Page 59: ...289 Next Steps Next Steps In the next chapter you learn a myriad of techniques for handling common questions and issues with pivot tables 11 ...

Page 60: ......

Search results

Summary of Contents for VBA

Reviews:

Related manuals for VBA

Brands by name

Popular brands

Amazon VBA, Manual

Search results

Summary of Contents for VBA

Reviews:

Related manuals for VBA

Brands by name

Popular brands