Microsoft KB Archive/213727

= XL2000: Show Pages Does Not Use Sheet.xlt for Worksheets =

Article ID: 213727

Article Last Modified on 11/23/2006

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q213727





SYMPTOMS
If you use the Show Pages shortcut menu command to create separate worksheets for all the items for a page field in a PivotTable, the worksheets that are created are not based on any template files that may be on the computer.



RESOLUTION
To use an existing worksheet template when you use the Show Pages menu item to create new worksheets, create and run a macro that simulates the Show Pages functionality. See the "More Information" section in this article for a sample macro that simulates the command.

NOTE: There is no way to manually force the Show Pages command to create new worksheets using an existing worksheet template.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



MORE INFORMATION
The following example demonstrates how to create a sample worksheet template, a PivotTable, and a Visual Basic for Applications macro that simulates the Show Pages command.

Creating the Worksheet Template
To create a sample worksheet template, use the following steps:  Close and save any open workbooks, and then create a new workbook. Delete all but one worksheet in the workbook. On the File menu, click Page Setup. In the Page Setup dialog box, click the Header/Footer tab. In the Header/Footer tab, click Custom Header. In the Left section box type this is a test, and then click OK. Click OK in the Page Setup dialog box.</li> On the File menu, click Save.</li> In the Save As dialog box, click Template in the Save as type list.</li> In the File Name box, type Sheet .</li> In the Save in list, locate and click the XLstart folder to select it.

This folder is located in the following default location:

Program Files\Microsoft Office\Office\XLstart

</li> Click Save.</li> On the File menu, click Close.</li></ol>

Creating the PivotTable
To create a sample PivotTable, use the following steps: <ol>  Create a new workbook and then enter the following data in Sheet1: <pre class="fixed_text">  A1: Name    B1: Amount   C1: Area A2: Bob    B2: 1        C2: e   A3: Sue     B3: 1        C3: w   A4: Sue     B4: 1        C4: e   A5: Bob     B5: 1        C5: n   A6: Tom     B6: 1        C6: e   A7: Sue     B7: 1        C7: n   A8: Tom     B8: 1        C8: n   A9: Bob     B9: 1        C9: w                    </li> Click cell A1. Then click PivotTable and PivotChart Report on the Data menu.</li> In step 1 of the PivotTable Wizard, click Next.</li> In step 2 of the PivotTable Wizard, verify that the specified range is $A$1:$C$9, and then click Next.</li> In step 3 of the PivotTable Wizard, click Layout.</li> Drag the Name button onto the ROW field, drag the Amount button onto the DATA field, and then drag the Area button onto the PAGE field. Click OK.</li> Click Finish.</li></ol>

A PivotTable is created in a new worksheet in the workbook.

Sample Visual Basic for Applications Macro
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

https://partner.microsoft.com/global/30000104

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

To create the macro that simulates the Show Pages command, use the following steps: <ol> On the Tools menu, point to Macro, and then click Visual Basic Editor (or press ALT+F11).</li> On the Insert menu, click Module.</li>  Enter the following code in the module: Sub Show_Pages

Dim mysheet As String

' Reduce the amount of screen flickering. Application.ScreenUpdating = False

' Set the name of the worksheet with the PivotTable. mysheet = ActiveSheet.Name

With Sheets(mysheet).PivotTables(1)

' Loop through all items in the "Area" page field. For Each x In .PageFields("Area").PivotItems

.PivotFields("Area").CurrentPage = x.Name

' Insert a new worksheet using Sheet.xlt in the Xlstart ' folder. Sheets.Add Type:="Worksheet" ActiveSheet.Name = x.Name

' Activate the worksheet that contains the PivotTable. Sheets(mysheet).Select

' Select the PivotTable and copy it. .PivotSelect "", xlDataAndLabel Selection.Copy

' Activate the worksheet with the name from the item ' in the page field and paste the PivotTable with this ' page field item displayed. Sheets(x.Name).Select ActiveSheet.Paste

Next End With

End Sub </li> <li>On the File menu, click Close and Return to Microsoft Excel.</li> <li>Save your workbook.</li></ol>

Running the Macro
To use the sample macro, use the following steps:
 * 1) Switch to the worksheet that contains the PivotTable.
 * 2) Run the Show_Pages macro.

For the sample PivotTable that you created, the macro creates three new worksheets. Each worksheet uses a name that corresponds to an item in the Area page field and contains a view of the PivotTable that is set to the corresponding page field value.
 * 1) Switch to any of the newly created worksheets.
 * 2) On the File menu, click Page Setup.
 * 3) Click the Header/Footer tab in the Page Setup dialog box.

NOTE: The header is the same header that is specified in Sheet.xlt.

<div class="references_section">