Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    PivotTable for multiple sheets (2003)

    Hello,

    I have a workbook that has 15 worksheets with data on it. I need to create a pivot table for each sheet. The rows of data can vary from sheet to sheet, but all sheets only have 4 columns. Is there a way to create a macro to do this task? I tried recording a macro, but it refers to the sheet is recording as the place where the information is comming from. Any help would be great.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: PivotTable for multiple sheets (2003)

    Here is an example. It will loop thru all the worksheets in the workbook, creating a pivot table on a new sheet (you can change the Table destination if you want a different location). This creates a pagefield from the first column, a row from the second, a clumn from the third and uses the 4th col as a the data field. Adapt as desired.

    Steve

    <pre>Option Explicit
    Sub CreatePivotTables()
    Dim wTemp As Worksheet
    Dim PT As PivotTable
    Dim rTemp As Range

    For Each wTemp In Worksheets
    Set rTemp = wTemp.Range("A1").CurrentRegion
    Set PT = wTemp.PivotTableWizard(SourceType:=xlDatabase, _
    SourceData:=rTemp, TableDestination:="")
    With PT
    .AddFields _
    PageFields:=rTemp(1, 1), _
    RowFields:=rTemp(1, 2), _
    ColumnFields:=rTemp(1, 3)
    .PivotFields(rTemp(1, 4).Value) _
    .Orientation = xlDataField
    End With
    Next
    Set PT = Nothing
    Set rTemp = Nothing
    Set wTemp = Nothing
    End Sub</pre>


  3. #3
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PivotTable for multiple sheets (2003)

    Thanks Steve. One quick question. I get the following error when I try to run the command above. I get a compile error with a message that says a module is not a valid type at the statement "Dim PT as PivotTable". I know I am doing something wrong. I was also wondering if there is a way to leave the table in each sheet without having to create a new sheet for each pivot table. I was thinking of putting the information in all cells F5 of each worksheet. Any help would be great.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: PivotTable for multiple sheets (2003)

    1) I am not sure why you get that error, I can not replicate it. In my test file it works as expected. Could you attach a sample file which shows the error?

    2) As mentioned in the original post, to change the location of the pivot tables, give a destination for it. The following line will put it in the worksheet in G1.

    <pre> Set PT = wTemp.PivotTableWizard(SourceType:=xlDatabase, _
    SourceData:=rTemp, TableDestination:=wTemp.Range("G1"))</pre>


    Steve

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: PivotTable for multiple sheets (2003)

    Regarding your first error, try deleting the line and retyping
    <code> Dim PT as </code>
    and once you type the final space after the 'as', a drop down list should appear - is PivotTable an item in the list?
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •