Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Mar 2014
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy Macro to create privot table

    Hello,

    Trying to create a macro that creates a pivot table with the number of rows changing depending on the data set in the download tab.

    Am very new to macros and am enclosing the code that I used but isn't automatically picking up the missing rows.

    Sub PivotTable()
    '
    ' PivotTable Macro
    '

    '
    Sheets("Download").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
    "Download!R1C1:R612C24", Version:=xlPivotTableVersion14).CreatePivotTable _
    TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
    :=xlPivotTableVersion14
    Sheets("Sheet1").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Invoice Date")
    .Orientation = xlColumnField
    .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataFiel d ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("Post Bundle Cost"), "Sum of Post Bundle Cost", _
    xlSum
    With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Username")
    .Orientation = xlRowField
    .Position = 1
    End With
    Range("E5").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields ("Username").AutoSort _
    xlDescending, "Sum of Post Bundle Cost", ActiveSheet.PivotTables("PivotTable1") _
    .PivotColumnAxis.PivotLines(4), 1
    End Sub


    Please help.

  2. #2
    Silver Lounger
    Join Date
    Mar 2014
    Location
    Forever West
    Posts
    2,075
    Thanks
    0
    Thanked 259 Times in 248 Posts
    Which spreadsheet program and which version? Some help pages found by Google Search of Excel 2010:
    https://www.google.com/search?noj=1&...16.stOc_u9kT3M
    This may be too simple for your need:
    http://www.dummies.com/how-to/conten...Id-223716.html

  3. #3
    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
    I think this can do what you want. I made the range dynamic and defined at runtime. i also cleaned up some other items as well to eliminate the selections, the defined sheet name etc.
    Code:
    Option Explicit
    Sub PivotTable()
      Dim rData As Range
      Dim PT As PivotTable
      With Sheets("Download")
        Set rData = .Range(.Range("A1"), .Range("A1").End(xlToRight))
        Set rData = .Range(rData, rData.End(xlDown))
      End With
    
      Set PT = ActiveSheet.PivotTableWizard _
        (SourceType:=xlDatabase, _
          SourceData:=rData, _
          TableDestination:="", _
          TableName:="PivotTable1")
      With PT
        With .PivotFields("Invoice Date")
          .Orientation = xlColumnField
          .Position = 1
        End With
        .AddDataField .PivotFields("Post Bundle Cost"), _
          "Sum of Post Bundle Cost", xlSum
        With .PivotFields("Username")
          .Orientation = xlRowField
          .Position = 1
        End With
        Range("E5").Select
        .PivotFields("Username").AutoSort xlDescending, _
          "Sum of Post Bundle Cost", .PivotColumnAxis.PivotLines(4), 1
      End With
    End Sub
    Steve

Posting Permissions

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