CODEWITHSUNDEEP
excelvbapivot-table
ggmkp
ggmkp

Reputation: 725

Excel VBA - Generalize Pivot Source Data / Range

I recorded the pivot table macro and I'm trying to generalize source data instead of going off of sheet name "REPORTS"

It grabs all the data from active sheet despite what the name of the sheet.

This way I can use the macro to create a pivot table for any active sheet:-

Sheets("**REPORTS**").Select
Range("A1").Select
Sheets.Add.Name = "Pivot"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    Sheets("**REPORTS**").Range("A1").CurrentRegion, Version:=xlPivotTableVersion15).CreatePivotTable _
    TableDestination:="Pivot!R3C1", TableName:="PivotTable1", DefaultVersion _
    :=xlPivotTableVersion15
Sheets("Pivot").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1")
    .InGridDropZones = True
    .RowAxisLayout xlTabularRow
End With

Upvotes: 0

Views: 25769

Answers (2)

Tim Williams
Tim Williams

Reputation: 166256

Sub TT()

    Dim shtSrc As Worksheet, shtDest As Worksheet
    Dim pc As PivotCache

    Set shtSrc = ActiveSheet

    Set shtDest = shtSrc.Parent.Sheets.Add()
    shtDest.Name = shtSrc.Name & "-Pivot"

    Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=shtSrc.Range("A1").CurrentRegion)
    pc.CreatePivotTable TableDestination:=shtDest.Range("A3"), _
        TableName:="PivotTable1"

    With shtDest.PivotTables("PivotTable1")
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
    End With

End Sub

Upvotes: 1

Ripster
Ripster

Reputation: 3585

This will not add any data to the pivot table but it will create it

Sub Example()
    Dim PrevSheet As Worksheet
    Set PrevSheet = ActiveSheet
    Range("A1").Select
    Sheets.Add.Name = "Pivot"
    PrevSheet.Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                      SourceData:=ActiveSheet.UsedRange, _
                                      Version:=xlPivotTableVersion15).CreatePivotTable _
                                      TableDestination:="Pivot!R3C1", _
                                      TableName:="PivotTable1", _
                                      DefaultVersion:=xlPivotTableVersion15
    Sheets("Pivot").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1")
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
    End With
End Sub

Upvotes: 0

Related Questions