none
Pivot Table from no. Selected tables in Excel Macro RRS feed

  • Question

  • Hi!

    I am trying to create a macro that will automatically generate a pivot table based on different selected tables in Excel, and position the pivot table on the same first row of the selected table, and +2 from the last column of the selected table.

    For example, I have one table that contains the ff:

    Business Type
     A. Platinum  B. Gold C. Silver D. Bronze
    CORP 36% 32% 28% 25%
    LLC 5% 6% 5% 5%
    INC 2% 2% 1%

    1%

    and another table:

     

    ethnicity A. Platinum B. Gold C. Silver D. Bronze
    ENGLISH 3% 2% 2% 2%
    IRISH 1% 0% 0% 0%
    GERMAN 1% 1% 0% 1%

    and I want my pivot table to display like this:

     

    Index
    Business Type Sum of Index1 Sum of Index2
    CORP 279 419
    INC 190 48
    LLC 73 119
    PARTNER 122 276
    SOLE 14 158

    here's the my source code (not complete)...

    Sub Macro11()
    '
    ' Macro11 Macro
    '
    Dim FirstRow As Long
    Dim LastCol As Long
    Dim PtRange As Range
    Dim UsdRange As Range
    Dim PivotTblname As String
    
    '
    Set UsedRng = ActiveSheet.UsedRange
    FirstRow = UsedRng(UsedRng.Cells.Row).Row
    LastCol = UsedRng(UsedRng.Cells.Count).Column
    Set PtRange = Cells(FirstRow, LastCol + 2)
    
    
    
      ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Selection.Address, Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:=PtRange, TableName:="PivotTable9", DefaultVersion _
        :=xlPivotTableVersion12
            
      Sheets("sheet2").Select
      Cells(1, 13).Select
      ActiveWindow.ScrollColumn = 2
      ActiveWindow.ScrollColumn = 3
      With ActiveSheet.PivotTables("PivotTable9").PivotFields( _
        FirstRow)
        .Orientation = xlRowField
        .Position = 1
      End With
      ActiveWindow.SmallScroll ToRight:=1
      ActiveSheet.PivotTables("PivotTable9").CalculatedFields.Add "Index1", _
        "=A__Platinum /F__Overall_VDBS_Universe *100", True
      ActiveSheet.PivotTables("PivotTable9").PivotFields("Index1").Orientation = _
        xlDataField
      ActiveSheet.PivotTables("PivotTable9").CalculatedFields.Add "Index2", _
        "=G__Designers /I__VDBS_Universe *100", True
      ActiveSheet.PivotTables("PivotTable9").PivotFields("Index2").Orientation = _
        xlDataField
      With ActiveSheet.PivotTables("PivotTable9").PivotFields("Sum of Index1")
        .NumberFormat = "#,##0"
      End With
      With ActiveSheet.PivotTables("PivotTable9").PivotFields("Sum of Index2")
        .NumberFormat = "#,##0"
      End With
      Range("N1").Select
      ActiveSheet.PivotTables("PivotTable9").DataPivotField.Caption = "Index"
      Range("N2").Select
    End Sub

    Anyone, can help me this? thank you so much

    Friday, June 24, 2011 8:21 PM

All replies