Amazon VBA Bedienungsanleitung

IN THIS CHAPTERUsing VBA to Create PivotTables11Introducing VBAVersion 5 of Excel introduced a powerful newmacro language called Visual Basic for Appl

Chapter 11 Using VBA to Create Pivot Tables24011In Excel 2000 and newer, you first build a pivot cache object to describe the input area ofthe data:D

241Building a Pivot Table in Excel VBAYou can now run through the steps needed to lay out the pivot table. In the .AddFieldsmethod, you can specify on

‘ Set up the data fieldsWith PT.PivotFields(“Revenue”).Orientation = xlDataField.Function = xlSum.Position = 1End WithAt this point, you’ve given VBA

243Building a Pivot Table in Excel VBASet PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=

Determining Size of a Finished Pivot TableKnowing the size of a pivot table in advance is difficult. If you run a report of transactionaldata on one d

245Building a Pivot Table in Excel VBADim PT As PivotTableDim PRange As RangeDim FinalRow As LongSet WSD = Worksheets(“PivotTable”)‘ Delete any prior

WSD.ActivateRange(“R1”).SelectEnd SubThe preceding code creates the pivot table. It then copies the results as values and pastesthem as values in R10:

247Creating a Report Showing Revenue by ProductHere are just a few of the annoyances that most pivot tables present in their default state: The Outli

Ensuring Table Layout Is UtilizedIn previous versions of Excel, multiple row fields appeared in multiple columns. Three lay-outs are now available. Th

249Creating a Report Showing Revenue by ProductWhen you have large numbers, displaying the thousands separator helps the person readingthe report. To

 Enable all macros (not recommended; potentially dangerous code can run)—this settingis equivalent to low macros security in Excel 2003. Because it c

A different technique is to turn on the first subtotal. This method automatically turns offthe other 11 subtotals. You can then turn off the first sub

251Handling Additional Annoyances When Creating Your Final Reportvariables to the original workbook, new workbook, and first worksheet in the new work

Note that you use the Paste Special option to paste just values and number formats. Thisgets rid of both borders and the pivot nature of the table. Yo

253Handling Additional Annoyances When Creating Your Final Report‘ Do some basic formatting‘ Autofit columns, bold the headings, right-alignSelection.

Dim TotColumns()Dim I as IntegerFinalCol = Cells(3, Columns.Count).End(xlToLeft).ColumnReDim Preserve TotColumns(1 To FinalCol - 2)For i = 3 To FinalC

255Handling Additional Annoyances When Creating Your Final Report‘ Create the Pivot Table from the Pivot CacheSet PT = PTCache.CreatePivotTable(TableD

.Font.Size = 14End With‘ Copy the Pivot Table data to row 3 of the Report sheet‘ Use Offset to eliminate the title row of the pivot tablePT.TableRange

257Addressing Issues with Two or More Data Fields11Figure 11.11Converting 50,000 rowsof transactional data tothis useful report takesless than two sec

With PT.PivotFields(“Units”).Orientation = xlDataField.Function = xlSum.Position = 2.NumberFormat = “#,##0”End WithChapter 11 Using VBA to Create Piv

259Addressing Issues with Two or More Data Fieldsalways done at the summary level. If you define a calculated field for average price asRevenue divide

233Introducing VBA11Figure 11.2The Visual Basic Editorwindow is lurkingbehind every copy ofExcel shipped since 1993.Project ExplorerProperties windowC

‘ Set up the row fieldsPT.AddFields RowFields:=”Product”, ColumnFields:=”Data”‘ Define Calculated FieldsPT.CalculatedFields.Add Name:=”AveragePrice”,

261Addressing Issues with Two or More Data Fields11Figure 11.14The virtual Data dimen-sion contains two fieldsfrom your dataset plus acalculation.It i

‘ Define input area and set up a Pivot CacheFinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).RowFinalCol = WSD.Cells(1, Columns.Count). _End(xlToLeft).Co

263Summarizing Date Fields with Grouping11Figure 11.16After the componentsthat make up the calcu-lated Plants Group itemare hidden,the total rev-enue

Creating a group with VBA is a bit quirky. The .Group method can be applied to only a sin-gle cell in the pivot table, and that cell must contain a da

265Summarizing Date Fields with Grouping‘ Set up the row fieldsPT.AddFields RowFields:=”InvoiceDate”, ColumnFields:=”Region”‘ Set up the data fieldsWi

Listing 11.7 Code That Uses the Group Feature to Roll Daily Dates Up to Weekly DatesSub ReportByWeek()‘ Listing 11.7Dim WSD As WorksheetDim PTCache As

267Using Advanced Pivot Table Techniques‘ Ensure that we get zeros instead of blanks in the data areaPT.NullString = “0”‘ Calc the pivot table to allo

Listing 11.8 Code Used to Create the Top 5 Markets ReportSub Top5Markets()‘ Listing 11.8‘ Produce a report of the top 5 marketsDim WSD As WorksheetDim

269Using Advanced Pivot Table Techniques.Position = 1.NumberFormat = “#,##0”.Name = “Total Revenue”End With‘ Ensure that we get zeros instead of blank

 Despite the aforementioned error checking, Excel may still encounter an error at run-time. If this happens, click the Debug button. The line that ca

The Top 5 Markets report actually contains two snapshots of a pivot table. After using theAutoShow feature to grab the top five markets with their tot

271Using Advanced Pivot Table Techniques‘ Delete any prior pivot tablesFor Each PT In WSD.PivotTablesPT.TableRange2.ClearNext PTWSD.Range(“R1:AZ1”).En

MsgBox “Detail reports for top 3 customers have been created.”End SubCreating Reports for Each Region or ModelA pivot table can have one or more Repor

273Using Advanced Pivot Table TechniquesPT.TableRange2.Offset(3, 0)PT.TableRange1.Offset(1, 0)Which you use is your preference, but if you use TableRa

‘ Turn off updating while building the tablePT.ManualUpdate = True‘ Set up the row fieldsPT.AddFields RowFields:=”Customer”, ColumnFields:=”Data”, _Pa

275Using Advanced Pivot Table TechniquesWSR.Cells(LastRow, 1).Value = “Top 5 Total”‘ Do some basic formatting‘ Autofit columns, bold the headings, rig

PivItem.Visible = TrueCase ElsePivItem.Visible = FalseEnd SelectNext PivItemControlling the Sort Order ManuallyIf your company has been reporting regi

277Creating Report Percentages.Function = xlAverage.Position = 3.NumberFormat = “#,##0”.Name = “Average Revenue”End WithWith PT.PivotFields(“Revenue”)

Percentage Growth from Previous MonthWith ship months going down the columns, you might want to see the percentage of rev-enue growth from month to mo

279Using New Pivot Table Features in Excel 2007Figure 11.22 shows the results of a pivot table with three custom calculation settings, as dis-cussed e

235Learning Tricks of the TradeWriting Code to Handle Any Size Data RangeThe macro recorder hard-codes the fact that your data is in a range, such as

To clear the filter from the Branch field, use the ClearAllFilters method:PT.PivotFields(“Branch”).ClearAllFiltersTo apply a date filter to the invoic

281Using New Pivot Table Features in Excel 2007PT.PivotFields(“Branch”).PivotFilters.Add _Type:=xlValueIsBetween, _DataField:=PT.PivotFields(“Sum of R

xlCaptionContains Filters for all captions that contain the specifiedstringxlCaptionDoesNotBeginWith Filters for all captions that do not begin withth

283Using New Pivot Table Features in Excel 2007xlDateNextMonth Filters for all dates that apply to the next monthxlDateNextQuarter Filters for all dat

xlValueIsNotBetween Filters for all values that are not between a specified range of valuesxlYearToDate Filters for all values that are within one yea

285Using New Pivot Table Features in Excel 2007Changing the LayoutThe Layout group of the Design ribbon contains four drop-downs. These drop-downs con

PT.RowAxisLayout xlTabularRowPT.RowAxisLayout xlOutlineRowPT.RowAxisLayout = xlCompactRowIn Excel 2007, you can add a blank line to the layout after e

287Using New Pivot Table Features in Excel 2007‘ Delete any prior pivot tablesFor Each PT In WSD.PivotTablesPT.TableRange2.ClearNext PTWSD.Range(“R1:A

Understanding Special Considerations for Excel 97Pivot tables and VBA took a radical turn in Excel 2000. In Excel 2000, Microsoft introducedthe PivotC

289Next StepsNext StepsIn the next chapter, you learn a myriad of techniques for handling common questions andissues with pivot tables.1112_0789736012

End+Down Versus End+UpYou might be tempted to find the final row by starting in cell A1 and using the End key in conjunction with the down-arrow key.

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

237Understanding VersionsNew in Excel 2007Although the basic concept of pivot tables is the same in Excel 2007 as it was in Excel 2003,several new fea

AllowMultipleFilters Indicates whether a pivot field can have multiplefilters applied to it at the same time.CompactLayoutColumnHeader Specifies the

239Building a Pivot Table in Excel VBAShowDrillIndicators Specifies whether drill indicators are shown inthe pivot table.ShowTableStyleColumnHeaders