XL98: Show Pages Does Not Use Sheet Template for Worksheets

• Microsoft Excel 98 Macintosh Edition

SYMPTOMS

If you use the Show Pages shortcut menu command to create separate worksheets for all of 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.

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

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

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

The following example creates a sample worksheet template, a PivotTable, and a Visual Basic for Applications macro that simulates the Show Pages command.

Creating the Worksheet Template

1. Close and save any open workbooks, and then create a new workbook.



2. Delete all but one worksheet in the workbook.



3. On the File menu, click Page Setup.



4. In the Page Setup dialog box, click the Header/Footer tab. In the Header/Footer tab, click Custom Header.



5. In the Left Section box type this is a test, and then click OK. Click OK in the Page Setup dialog box.



6. On the File menu, click Save.



7. In the Save As dialog box, click Template in the Save File as Type list.



8. In the Save as box, type Sheet.



9. In the Save In list, locate and click the Excel folder to select it.
   
   This folder is located in the following default location:

   Microsoft Office 98:Office:Startup:Excel




10. Click Save.



11. On the File menu, click Close.

Creating the PivotTable

1. Create a new workbook, and then enter the following data in Sheet1:

   
         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 




2. Click cell A1. Then, click PivotTable Report on the Data menu.



3. In step 1 of the PivotTable Wizard, click Next.



4. In step 2 of the PivotTable Wizard, verify that the specified range is $A$1:$C$9, and then click Next.



5. In step 3 of the PivotTable Wizard, 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.



6. Click Finish.

Sample Visual Basic for Applications Macro

http://www.microsoft.com/support/supportnet/overview/overview.asp

1. On the Tools menu, point to Macro, and then click Visual Basic Editor. Or, press OPTION+F11.



2. On the Insert menu, click Module.



3. 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 in the Startup
                     ' 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 




4. On the File menu, click "Close and Return to Microsoft Excel."



5. Save your workbook.

Running the Macro

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.



3. Switch to any of the newly created worksheets.



4. On the File menu, click Page Setup.



5. Click the Header/Footer tab in the Page Setup dialog box.

REFERENCES

For more information about PivotTable page fields, click Contents And Index on the Help menu (or on the Balloon Help menu if you are using a version of the Macintosh operating system earlier than 8.0), click the Index button in Microsoft Excel Help, type the following text

page fields

Additional query words: XL98

Keywords : kbcode kbprg kbdta xllist
Version : MACINTOSH:98
Platform : MACINTOSH
Issue type : kbbug