Daljit Dhadwal

Credit: ©iStockphoto.com/meckimac

Here’s some data in Excel 2007 copied from the Northwind Traders database that comes with Microsoft Access.

In this post, I’ll cover how to create separate worksheets that only show the information for people having a specific job title.

The final result will look like this:

This can easily be accomplished in a few steps with some VBA code.

1. Make a pivot table on a new worksheet with Job Title as the Report Filter and Company Name as what’s being counted.

2. Rename the worksheet with the pivot table on it as “main data”.

3.  Run the following VBA code. This is based on code originally from Debra Dalgleish.

Sub ExpandPivot()

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Set pt = Sheets("main data").PivotTables.Item(1)
    For Each pf In pt.PageFields
        For Each pi In pf.PivotItems
            pt.PivotFields(pf.Name).CurrentPage = pi.Name
            Range("A4").Select
            Selection.ShowDetail = True
            Sheets("main data").Select
        Next
    Next pf

End Sub

4. After running the above code, you’ll have separate worksheets with only the data for each job title. The new worksheets will have generic names such as “Sheet5”. These new sheets can easily be given new names based on the job title.

4a. Delete the worksheet with the pivot table (i.e., the “main data” sheet), the source worksheet which has data on all job titles, and any blank sheets in the workbook. The workbook should only have the newly created worksheets.

4b. Run the following code (based on code from here) to rename the worksheets:

Sub ReNameWorksheets()
     For Each sht In ThisWorkbook.Worksheets
            sht.Name = sht.Range("A2")
            sht.Activate
            Cells.EntireColumn.AutoFit
            Range("A1").Select
     Next sht
End Sub

The above code is pulling the name for each worksheet from the value in cell A2. The one thing you’ll need to watch out for is values that violate the rules for worksheet names in Excel. A worksheet name can be at most 31 characters and can’t have any of the following characters:  \ / ? *[]