Working with PivotTable Reports


An Excel table is like an X-ray photograph. It’s an image, but it’s a static image. If the rows and columns aren’t properly defined, the person reviewing the report might miss important relationships. A PivotTable report, in contrast, is like a CAT scan. It’s a multidimensional view of the data that enables you to find the most meaningful perspective. PivotTable reports are particularly useful for analytical reporting.

See Also 

The macros from this PivotTable report section were used when I created the Enterprise Information System (EIS) application described in the Appendix.

Create a PivotTable Report from an Internal Source

As with a table, you can make a PivotTable report retrieve its data from either an Excel list or from an external data source. Retrieving data from a list is relatively simple, but a PivotTable report manages the data somewhat differently than a table does.

  1. Close other workbooks so that the Chapter05 workbook is active. Then run the OpenOrdersFile macro to get a new orders list file.

    Troubleshooting 

    If you don’t have an OpenOrdersFile macro, see the section titled “Create a New File from an Existing Worksheet,” earlier in this chapter.

  2. Start recording a macro named zPivotFromInternal.

  3. On the Insert tab of the Ribbon, click PivotTable, and click OK to accept the default options.

    By default, a new PivotTable report displays an empty placeholder on the work-sheet, and the PivotTable Field List appears on the right.

    image from book

  4. Stop the recorder, and look at the macro in Visual Basic.

  5. In the Project Explorer, double-click Module1 in the Chapter05 workbook. Then, in the Immediate window, type OpenOrdersFile and press Enter.

    A new copy of the Orders file opens. You can run a macro from Visual Basic by simply stating the macro name.

  6. Clear the contents of the Immediate window, create the shell of a macro that looks like the following, and press F8 twice to step to the End Sub statement.

    Sub PivotFromInternal()   Dim pc As PivotCache End Sub 

    You’re now ready to start entering the statements for the body of the macro.

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _   SourceData:= "Orders!R1C1:R3266C7", _   Version:=xlPivotTableVersion12).CreatePivotTable _  

    Ignoring the statement that creates a new sheet, the recorded macro’s next statement-which extends onto multiple lines-actually has two parts. It first adds a new item to the PivotCaches collection, using the word Create instead of the typical Add. Notice the opening parenthesis after the Create method. Then look for the corresponding closing parenthesis (just following the word xlPivotTableVersion12). After the closing parenthesis, there’s a CreatePivotTable method, which has its own arguments.

  7. Type (on one line) Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, Range("A1").CurrentRegion) and press Enter.

    Nothing appears to happen, but you now have a memory cache loaded and ready to create a PivotTable report. Rather than using an explicit range address for the source as the recorded macro did, you use the current region surrounding cell A1.

    A PivotTable report has a memory cache to store the data. This is different from a table. For a table, all the data is right there in the table-there is nothing hidden. For a PivotTable report, the data is actually stored in the memory cache. The name cache is appropriate, because it means “a hidden storage place.” You can create multiple PivotTable reports that use the same memory cache.

  8. Type Worksheets.Add and press Enter.

    This creates a new, blank worksheet. In the recorded macro, adding a new worksheet came at the beginning, but you don’t need it to create the memory cache, so you can move it lower in your macro.

      Version:=xlPivotTableVersion12).CreatePivotTable _   TableDestination:="Sheet2!R3C1", _   TableName:="PivotTable1", _   DefaultVersion:=xlPivotTableVersion12  
  9. Type pc.CreatePivotTable ActiveCell and press Enter.

    This creates the shell of the PivotTable report. The only required argument for the CreatePivotTable method is the TableDestination. Because you can create multiple PivotTable reports from a single cache, you need to specify a location for each one.

  10. In the Immediate window, execute the Worksheets.Add and pc.CreatePivotTable ActiveCell statements a second time.

    You end up with two different worksheets, each of which contains the shell of a PivotTable report. They’re both connected to the same memory cache.

  11. Press F5 to end the current macro. Then copy the statements from the Immediate window into the macro, and press F5 to test the macro.

One of the powerful features of a PivotTable report is the ability to create multiple views of the same data-without needing multiple copies of the data.

Create a PivotTable Report from an External Source

Creating a PivotTable report based on an external source is similar to creating a table based on an external source. With a table, however, you assign the source connection string to the ListObject and the table name to the QueryObject that belongs to it. With a PivotTable report, you assign both the source connection string and the table name to the PivotCache object.

  1. In Excel, close other workbooks so that the Chapter05 workbook is active.

  2. Start recording a new macro named zPivotFromExternal.

  3. Press Ctrl+N to open a new workbook. On the Data tab of the Ribbon, click From Access, navigate to the folder containing the practice files for this book, select Orders.accdb, and click Open. In the Import Data dialog box, select the PivotTable Report option, and click OK.

    image from book

  4. Stop the recorder, and look at the macro.

    The top part of the macro has an array similar to the one in the macro where you created a table from an external data source, but this time, it is part of creating a PivotCache object. In addition to creating a memory cache for the PivotTable, this macro also adds an item to the workbook’s Connections collection. The name fora single item in the Connections collection is not Connection, as you might expect, but rather WorkbookConnection. As usual, if you assign the objects to variables, you can make the macro easier to read.

  5. Clear the contents of the Immediate window, create the shell of a macro that looks like the following, and press F8 twice to step to the End Sub statement.

    Sub PivotFromExternal()   Dim wc As WorkbookConnection   Dim pc As PivotCache   Dim s As String  End Sub 

You’re now ready to start entering the statements for the body of the macro.

Workbooks.Add  
  1. In the Immediate window, type Workbooks.Add and press Enter.

    Workbooks("Book2").Connections.Add "Orders", "", Array( _   ...   "Support Complex Data=False"), Array("Orders"), 3  
  2. Type the following two statements, and press Enter after each one.

    s = ThisWorkbook.Path & "\..\Orders.accdb" s = "OLEDB;Provider= Microsoft.ACE.OLEDB.12.0;Data Source=" & s 

    These statements are exactly the same as the ones that set the source string in the TableFromExternal macro earlier in this chapter. Notice that in the recorded macro, some of the arguments for the Add method come before the array and some come after the array. By putting the data source into a string, it’s easier to see the whole structure of the Add method.

  3. Type (on one line) Set wc = ActiveWorkbook.Connections.Add("", "", s,"Orders", 3) and press Enter.

    The first argument of the Add method is the Name argument. It is required, and the recorder inserted "Orders" as the name. But if you use an empty string for the argument, Add will generate a default name-automatically generating a new unique name if you create multiple connections in the same workbook. The arguments after the source string are the same as in the recorded statement.

    Tip 

    Creating an explicit connection in the macro is one way that a PivotTable report is different from a table. When you create a table from an external connection, the QueryTable object actually does create a new WorkbookConnection, but it creates it implicitly, without putting it in the macro. When you create a PivotTable report from an external connection, the macro explicitly creates a WorkbookConnection object and then links the PivotCache to it.

    ActiveWorkbook.PivotCaches.Create( _   SourceType:=xlExternal, _   SourceData:=ActiveWorkbook.Connections("Orders"), _   Version:=xlPivotTableVersion12).CreatePivotTable  
  4. Type (on one line) Set pc = ActiveWorkbook.PivotCaches.Create(xlExternal, wc) and press Enter.

    As with the Create method for creating a memory cache from an internal source, most of the arguments are optional. From an external source, you use the reference to the workbook connection as the source. By assigning the memory cache to a variable, you can split the CreatePivotTable method into a new statement.

      Version:=xlPivotTableVersion12).CreatePivotTable _   TableDestination:="Sheet1!R1C1", _   TableName:="PivotTable4", _   DefaultVersion:=xlPivotTableVersion12  
  5. Type the following two statements, pressing Enter after each one.

    Worksheets.Add pc.CreatePivotTable ActiveCell 

    Creating a PivotTable from an external connection is exactly the same as creating one from an internal connection-it’s the memory cache that is different when you access an external source.

  6. Press F5 to end the current macro. Then copy the statements from the Immediate window into the macro, and press F5 to test the macro.

Multiple PivotTable reports can access the same memory cache, and thus the same workbook connection. If you need to change the source for the connection, just change the one connection object and all the dependent PivotTable reports will change appropriately.

Record a Macro to Set the PivotTable Structure

When you first create a PivotTable report, you get an empty shell on the worksheet, along with a Field List task pane to help you create the layout. You can use the macro recorder to help you learn the methods and properties for defining the PivotTable structure.

  1. Run the PivotFromExternal macro to create a new workbook with the shell of a PivotTable report.

  2. Start recording a macro with the name zPivotSetStructure.

  3. In the PivotTable Field List, select the Net check box.

    Because the field is numeric, it generates a data field in the Values list named Sum of Net. The name Sum of Net also appears in cell A1, along with the grand total in cell A2. You can change the name of the data field by simply typing over it.

  4. In cell A1, type Net plus a trailing space, and then press Enter.

    You can rename a field by typing the new name on the report, but you cannot give a data field the same name as any of the database fields. By adding a space after the name, you can make the name appear to be the same as the field name.

    image from book

  5. In the field list, select the Category check box.

    The category names appear in column A. Because the field is text, it defaults to the Row Labels area.

  6. In the field list, drag the State field to the Report Filter area.

    The state field moves above the report, and the rest of the report shifts down to make room. If you want a text field to go somewhere other than the Row Labels area, you need to explicity drag it.

  7. In the field list, drag the Date field to the Column Labels area.

    The dates appear across the top of the report. A PivotTable report recognizes dates and allows you to create automatic groupings for them.

  8. Right-click any of the date column labels and click Group. Clear Months, select Quarters and Years, and click OK.

    Note that there are seven possible grouping levels, and you selected the bottom two.

    image from book

  9. On the PivotTable Tools Options tab of the Ribbon, click Select, and then click Entire PivotTable. Click Select again, and click Values. Then, on the Home tab of the Ribbon, click Cell Styles and click Comma [0].

    This applies a basic number format with no decimals to just the data area of the PivotTable report.

    image from book

  10. Stop the recorder, and look at the macro.

You can now create a new macro by using information gleaned from the recorded macro.

Set the PivotTable Structure

The ability to control the structure of a PivotTable report from a macro is a powerful skill-particularly if you want to automate analytical reports. The recorder shows how you can manipulate the PivotTable report, but as usual, it does not always make the changes in the simplest way.

  1. Run the PivotFromExternal macro. Then clear the Immediate window, create the shell of a macro that looks like this, and press F8 three times to initialize the variable.

    Sub PivotSetStructure()   Dim pt as PivotTable   Set pt = ActiveCell.PivotTable End Sub 

    You’re now ready to create the statements for the body of the macro.

    ActiveSheet.PivotTables("PivotTable1").AddDataField _   ActiveSheet.PivotTables("PivotTable1"). _   PivotFields("Net"), "Sum of Net", xlSum  
  2. In the Immediate window, type pt.PivotFields("Net").Orientation = xlDataField and press Enter.

    The recorded macro uses AddDataField to convert the Net field into a data field. But simply assigning xlDataField to the Orientation property-by analogy with the other fields-works just as well and keeps the macro statements consistent.

    ActiveSheet.PivotTables("PivotTable1"). _   DataPivotField.PivotItems("Sum of Net").Caption = "Net "  
  3. Type pt.DataFields(1).Caption = "Net " and press Enter.

    Rather than refer to the data field as a PivotItem from the DataPivotField object, you can simply refer to it directly as a field in the DataFields collection. The simpler option makes the macro easier to read.

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("State")   .Orientation = xlPageField   .Position = 1 End With  

  4. Type the following three statements, pressing Enter after each one.

    pt.PivotFields("Category").Orientation = xlRowField pt.PivotFields("State").Orientation = xlPageField pt.PivotFields("Date").Orientation = xlColumnField 

    The recorded macro uses three different With structures to move the fields to the appropriate locations. Because there is only one field on each axis, it is not necessary to specify the Position property, so it’s not necessary to use a With structure or a variable.

    Range("B4").Select Selection.Group Start:=True, End:=True, _   Periods:=Array(False, False, False, False, False, True, True)  

  5. Type pt.ColumnFields("Date").DataRange.Cells(1).Group Periods:=Array(False, False, False, False, False, True, True) and press Enter.

    The Year and Quarter groups appear. The array corresponds to the seven levels of summary in the grouping dialog box. You want the last two items-quarter and year-which is why those two elements of the array are true. The recorder uses the cell address to find the column field, but that is a fragile way to write the macro. Using the Date field directly is much more robust.

    ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataOnly, True Selection.Style = "Comma [0]"  

  6. Type pt.DataBodyRange.Style = "Comma [0]" and press Enter.

    The recorded macro gets to the data range by using the PivotSelect method. You have already used a DataBodyRange property for the ListObject of an Excel table. With the assistance of Auto Lists, you can quickly determine that a PivotTable also has a DataBodyRange property, which, because it is a Range object, has a Style property.

  7. Press F5 to stop the macro. Then copy the statements from the Immediate window into the PivotSetStructure macro. Run the PivotFromExternal macro, and then run the PivotSetStructure macro to test it.

The recorded macro didn’t use the Orientation property for the data field, but because it used it for the other fields, you were able to check the enumeration list for the Orientation property to see if there was an option for the data field. Sometimes recording one action can give you a clue that helps simplify the way you write other statements.

Record a Macro to Customize a PivotTable Layout

By default, a PivotTable report automatically adjusts itself based on the specific datait contains-it automatically adjusts column widths and automatically hides rows and columns that don’t contain data. But sometimes you want a report that always has the same layout, regardless of what data it contains. For example, maintaining a consistent layout can make it easier to compare results when you switch the report from one state to another.

Troubleshooting 

If you don’t have a current version of a PivotTable report, run the PivotFromExternal and PivotSetStructure macros.

  1. Start recording a macro with the name zPivotSetLayout.

  2. Click the State report filter arrow in cell B2, select Idaho, and then click OK.

    Lucerne Publishing started selling products in Idaho only recently, so only some of the categories and dates have values. By selecting a state with limited data, you can make sure that the report shows the same grid regardless of the state.

    image from book

  3. Right-click any of the row labels-say, Dinosaur-and click Field Settings. Click the Layout & Print tab, select Show items with no data, and click OK.

    The Seattle category now appears-even though there is no data in the row.

  4. Right-click the 2007 column label, and click Field Settings. Click the Layout & Print tab, select Show items with no data, and click OK.

    All the available years now appear. You never want to show the less-than and greater-than options that date grouping automatically generates, and for the current reporting purposes, you want to see only the most recent two years.

    image from book

  5. Click the Column Labels arrow, and clear <1/1/2005, 2005, and >10/2/2007. In other words, clear all the boxes except the final two full years.

    image from book

  6. On the PivotTable Tools Design tab of the Ribbon, click Grand Totals, and then click On For Columns Only.

    The totals to the right of 2007 disappear. The total for just those two years is not a meaningful number.

    The PivotTable report typically autofits all the columns in the report. This avoids the possibility of seeing hash marks where the number is too big, but it is disruptive when you switch from one state to another. By setting all the columns just wider than the widest column, you can keep them all consistently at the same width.

  7. Click the State report filter arrow in cell B2, and then click All. Then select the columns from B to I, and double-click the border between column C and column D to autofit all the columns. Drag the border between column C and column D slightly to the right to set all the columns to the same size-slightly larger than the minimum that autofit detected.

    image from book

  8. Right-click any cell in the PivotTable report, and click PivotTable Options. On the Layout & Format tab, clear both the Autofit column widths on update and the Preserve cell formatting on update check boxes, and then click OK.

    If you don’t clear the Autofit option for column widths, the widths will automatically adjust again each time you change the state.

  9. On the PivotTable Tools Options tab of the Ribbon, click +/- Buttons to turn off the buttons, and click Field Headers to turn off the headers.

    image from book

    image from book

    When you have limited navigation for a report, you don’t need the extra user interface features offered by a PivotTable report.

  10. Turn off the recorder, and edit the zPivotSetLayout macro.

You can now use information you learned in the recorded macro to create one of your own that refines the layout of a PivotTable report.

Customize a PivotTable Layout

When working with a PivotTable report layout, the macro recorder does a good job. You will, of course, want to use an object variable for the PivotTable object. This macro uses pivot fields in several places, so it will be helpful to create a variable for a PivotField object. Rather than use range references-which may not always be the same-you can use reference points from within the PivotTable object.

  1. Run the PivotFromExternal and PivotSetStructure macros. Then in Visual Basic, create the shell of a macro that looks like the following, and press F8 four times to initialize the variables.

    Sub PivotSetLayout()   Dim pt As PivotTable   Dim pf as PivotField   Set pt = ActiveCell.PivotTable   Set pf = pt.ColumnFields("Years") End Sub 

    You’re now ready to create the statements for the body of the macro.

    ActiveSheet.PivotTables("PivotTable1").PivotFields("State"). _   ClearAllFilters   ActiveSheet.PivotTables("PivotTable1").PivotFields("State"). _   CurrentPage = "Idaho"  

  2. Type pt.PageFields("State").CurrentPage = "Idaho" and press Enter.

    This allows you to see the collapsed report so that you can verify the subsequent statements. You can leave it out of the final macro if you want.

    ActiveSheet.PivotTables("PivotTable1"). _   PivotFields("Category").ShowAllItems = True ActiveSheet.PivotTables("PivotTable1")._   PivotFields("Years").ShowAllItems = True  

  3. Type the following two statements, following each with Enter.

    pt.RowFields("Category").ShowAllItems = True pf.ShowAllItems = True 

    These statements are essentially the same. They show all the items for the row and column fields, and they differ from the recorded macro in that they use object variables.

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Years")   .PivotItems("<1/1/2005").Visible = False   .PivotItems("2005").Visible = False   .PivotItems(">10/2/2007").Visible = False End With  

  4. Type the following three statements, following each with Enter.

    pf.PivotItems(1).Visible = False pf.PivotItems(2).Visible = False pf.PivotItems(pf.PivotItems.Count).Visible = False 

    These hide the first two items and the last item in the Year field, leaving the last two full years. By using the Count of the number of PivotItems to find the last one, the macro will adapt more gracefully if the data set includes more than three years.

    ActiveSheet.PivotTables("PivotTable1").RowGrand = False ActiveSheet.PivotTables("PivotTable1").HasAutoFormat = False  

  5. Type the following two statements, following each with Enter.

    pt.RowGrand = False pt.HasAutoFormat = False 

    These are straightforward property assignments. The only change from the recorded macro is using the object variable.

    ActiveSheet.PivotTables("PivotTable1").PivotFields("State")._   ClearAllFilters ActiveSheet.PivotTables("PivotTable1").PivotFields("State").CurrentPage _   = "(All)"  

  6. Type pt.PageFields("State").CurrentPage = "(All)" and press Enter.

    This statement ensures that the values in the report are as large as possible. This statement is an important part of the macro because you don’t want to accidentally set the column width based on a filter with very small values. The recorder adds an extra statement to clear the existing filters, but that is not necessary.

    Columns("B:I").Select Columns("B:I").EntireColumn.AutoFit  
  7. Type pt.DataBodyRange.Columns(2).AutoFit and press Enter.

    The whole purpose of this statement is to find the width you should use for all the columns. The macro recorder changed all the columns, but you need to resize only the one you will use in the next statement, and you don’t need to change the selection to do it.

    Selection.ColumnWidth = 9.43  
  8. Type (on one line) pt.DataBodyRange.ColumnWidth = pt.DataBodyRange. Columns(2).ColumnWidth + 1 and press Enter.

    This adjusts all the columns of the data body range to be slightly wider than the one column you automatically adjusted in the previous step. You don’t want to use an absolute width; you want to use a width based on the AutoFit from the previous statement.

    ActiveSheet.PivotTables("PivotTable1").DisplayFieldCaptions = False ActiveSheet.PivotTables("PivotTable1").ShowDrillIndicators = False  

  9. Type the following two statements, pressing Enter after each one.

    pt.DisplayFieldCaptions = False  pt.ShowDrillIndicators = False 

    These statements differ from the recorded macro only in the use of the object variable.

  10. Press F5 to stop the macro. Then copy the statements from the Immediate window into the PivotSetLayout macro. Run the PivotFromExternal and PivotSetStructure macros, and then run the PivotSetLayout macro to test it.

  11. After running the macro, switch between different values in the State list to see that the report maintains its layout.

Making the columns consistently visible and consistently sized makes it easier for users to compare information between states.

Record a Macro to Customize a PivotTable Style

Excel 2007 has a powerful tool for applying a consistent style to an Excel table or a PivotTable report. You can select from 60 built-in styles for a table or from 84 built-in styles for a PivotTable report. You can also create new styles, with different formatting for different structured portions of the table or the PivotTable report. Table styles are also integrated with workbook themes, which means that once you apply a style, you can change the appearance dramatically but consistently simply by selecting a new theme.

  1. Start recording a macro with the name zPivotSetStyle.

  2. On the PivotTable Tools Design tab of the Ribbon, click the More arrow for PivotTable Styles.

  3. Without clicking, move the mouse pointer over Pivot Style Medium 23 (second column of the bottom row of the Medium section) and then over Pivot Style Dark 2 (second column of the top row of the Dark section).

    image from book

    The medium style highlights the first column but not the total row. The dark style highlights the total row but not the first column You want both. The solution is to create a duplicate of the dark style and then modify it to highlight the first column

  4. Right-click Pivot Style Dark 2, and then click Duplicate. Type NewPivotStyle as the name for the duplicated style, and click OK.

    This creates the duplicate of the style, but does not apply it to the report. You must explicitly apply the style to the report.

  5. Click the More arrow for PivotTable Styles again, and then click NewPivotStyle.

    Now you’re ready to modify the custom style.

  6. Right-click the NewPivotStyle and click Modify. In the Table Element list, select First Column.

  7. Click the Format button, and on the Fill tab, click the color swatch in the fourth row and fifth column of the main block of colors.

In this palette, tool tips don’t appear, but in a standard color palette, the tool tip for this swatch says Blue, Accent 1, Darker 25%.

This changes the first column to a darker color, but it also makes the font hard to read.

image from book

  1. On the Font tab, in the Font Style list, click Bold, and in the Color list, click White. Then click OK twice.

    This changes the font to white and bold so that it’s visible against the dark background.

    The styles are aligned with the Office themes. By switching from one theme to another on the Page Layout tab of the Ribbon, you can change the entire look and feel of the report. The font, however, does not change with the theme. The style uses the Normal cell style to define the font, and by default, the Normal style uses Arial. By changing the Normal cell style to use a theme font, you can completely synchronize your PivotTable report with the current theme.

  2. On the Home tab of the Ribbon, click the Cell Styles arrow, right-click Normal, and then click Modify. Click Format, and on the Font tab, in the Font list, select Calibri (Body). Then click OK twice.

    Even though the font name says Calibri (if you’re using the default Office theme), you’re really setting the font to the body font of the current theme, and that will change as you change themes.

  3. Stop the recorder.

  4. Before editing the macro, try out the report with various themes. Insert a new column A and give it a width of about 45 so that you can see the PivotTable report behind the theme list. Then on the Page Layout tab of the Ribbon, click the Themes button, and move the mouse pointer over the various themes to see the PivotTable change styles.

    image from book

  5. Edit the zPivotSetTheme macro.

You can now use information you learned in the recorded macro to create one of your own that changes the style for a PivotTable report.

Customize a PivotTable Style

The biggest enhancement you can make to the code produced by the macro recorder to modify a style is to eliminate statements that redefine properties that you didn’t change. Simplifying a macro so that it includes only the properties you actually change not only makes the macro much shorter, but it also clarifies the purpose of the macro: you don’t have to wonder which of the hundreds of properties was important to the macro.

  1. Run the PivotFromExternal and PivotSetStructure macros. (Running the PivotSetLayout macro is not essential before creating a macro that customizes the style, but doing so would not hurt anything.) Then clear the Immediate window, create the shell of a macro that looks like the folowing, and press F8 three times to initialize the variables.

    Sub PivotSetStyle()   Dim pt As PivotTable   Dim ts As TableStyle   Set pt = ActiveCell.PivotTable End Sub 

    You are now ready to start entering the statement in the Immediate window to create the body of the macro.

    ActiveWorkbook.TableStyles("PivotStyleDark2").Duplicate ("NewPivotStyle")  

  2. In the Immediate window, type (on one line) Set ts = ActiveWorkbook. TableStyles( "PivotStyleDark2").Duplicate("NewPivotStyle") and press Enter.

    This is no different from the recorded statement except that you assign the resulting style to a variable for future convenience.

    The recorded macro next includes about 26 With blocks that recreate all the elements of the duplicated style. You can ignore all the statements down to the one that assigns the NewPivotStyle to the current PivotTable report.

    ActiveSheet.PivotTables("PivotTable1").TableStyle2 = "NewPivotStyle"  

  3. Type pt.TableStyle2 = ts.Name and press Enter.

    This uses the objects instead of the longer reference. By using the Name property of the table style stored in the TableStyle variable, the macro will continue to work even if you change your mind about what name to use in the first statement of the macro.

    With ActiveWorkbook.TableStyles("NewPivotStyle").TableStyleElements( _   xlFirstColumn).Font   .FontStyle = "Bold"   .TintAndShade = 0   .ThemeColor = xlThemeColorDark1 End With  

  4. Type the following two statements (each on a single line), pressing Enter after each one.

    ts.TableStyleElements(xlFirstColumn).Font.FontStyle = "Bold" ts.TableStyleElements(xlFirstColumn).Font.ThemeColor = xlThemeColorDark1 

    These just set the font style to Bold and the color to white. One advantage of the Immediate window over the user interface is that you can see each change as you make it, without having to wait to close the dialog box.

    Tip 

    It’s ironic that the name for the white theme color is xlThemeColorDark1. The reason has to do with the fact that theme colors were developed primarily for Microsoft Office PowerPoint 2007. In PowerPoint, light colors on a dark background are common, so in a typical PowerPoint theme, this color would actually be dark.

    With ActiveWorkbook.TableStyles("NewPivotStyle").TableStyleElements( _   xlFirstColumn).Interior   .ThemeColor = xlThemeColorAccent1   .TintAndShade = -0.249946592608417 End With  

  5. Type the following two statements (each on a single line), pressing Enter after each one.

    ts.TableStyleElements(xlFirstColumn).Interior.ThemeColor = _   xlThemeColorAccent1 ts.TableStyleElements(xlFirstColumn).Interior.TintAndShade = -0.25 

    It takes two statements to implement a single color swatch from the theme colors palette. This is because the colors in a column of the palette are really just tints and shades of the accent color at the top. The implication is that from a macro, you are not limited to the five tint and shade variations in the theme palette. You can set the TintAndShade property to any level between -1 and 1, and the color will still shift appropriately when the workbook theme changes.

  6. Type ActiveWorkbook.Styles("Normal").Font.ThemeFont = xlThemeFontMinor and press Enter.

    The recorded macro includes almost two dozen statements just to set the theme font. In the Cell Style dialog box, you specified Calibri (Body) as the font, but the identifier for the font in the object model is the generic term xlThemeFontMinor. This is what enables the font to change to whatever specific font is defined in the current theme.

  7. Press F5 to stop the macro. Then copy the statements from the Immediate window into the PivotSetStyle macro. Run the PivotFromExternal, PivotSetStructure, and PivotSetLayout macros, and then run the PivotSetStructure macro to test it.

    After testing the macro, be sure to try out switching themes to make sure the colors and fonts change appropriately.

Excel tables and PivotTable reports are powerful tools for exploring and analyzing data, from both internal and external sources. The ability to use VBA macros to create and manipulate tables and PivotTables will enable you to develop stunning-and stunningly useful-tables for your own use and for the use of others.

CLOSE the Chapter05.xlsm workbook.



Microsoft Office Excel 2007 Visual Basic for Applications Step by Step
Microsoft Excel 2002 Visual Basic for Applications Step by Step (Step by Step (Microsoft))
ISBN: 0735613591
EAN: 2147483647
Year: 2004
Pages: 99
Authors: Reed Jacobsen

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net