CODEWITHSUNDEEP
excelvba
Cjamros
Cjamros

Reputation: 35

VBA -Place Pivot Table in Same worksheet

New to VBA and I am having difficulty with my Macro creating a pivot table on the same worksheet with my data (for example, starting the pivot table in the "I1" column). I have been able to run the macro to select all of the data within the sheet and then create this data on another worksheet. Since I will need to loop through a number of worksheets on the same workbook to create multiple pivot tables, having separate sheets isn't feasible.

Each worksheet has the same number of columns with a varying number of rows. My goal is to have this macro look at each worksheet and output a pivot table next to it.

I feel like the macro I have below is not referencing the correct pivot Table destination. Any assistance would be appreciated.

Sub Macro4()  
'  
'Macro4 Macro  
'

'  
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row  
DataSheet = ActiveSheet.Name

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
DataSheet & "!R1C1:R" & FinalRow & "C8", Version:=xlPivotTableVersion15). _
CreatePivotTable TableDestination:=DataSheet & "!R1C9", TableName:= _
"PivotTable4", DefaultVersion:=xlPivotTableVersion15

Sheets(DataSheet).Select  
Cells(1, 9).Select  
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Document Type") 
    .Orientation = xlRowField.Position = 1  
End With  
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Accounting Event")    
    .Orientation = xlRowField.Position = 2  
End With  
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Document Number")
    .Orientation = xlRowField.Position = 3  
End With  
ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables
( _"PivotTable4").PivotFields("Amount"), "Sum of Amount", xlSum

End Sub

Upvotes: 2

Views: 3362

Answers (1)

Shai Rado
Shai Rado

Reputation: 33692

Try the code below, it's with a little different approach, defining Range, PivotTable, PivotCache, and you can modify them easily according to your needs:

Sub Macro4()

Dim FinalRow            As Long
Dim DataSheet           As String
Dim PvtCache            As PivotCache
Dim PvtTbl              As PivotTable
Dim DataRng             As Range
Dim TableDest           As Range

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
DataSheet = ActiveSheet.Name

' set data range for Pivot Table
Set DataRng = Sheets(DataSheet).Range(Cells(1, 1), Cells(FinalRow, 8))  ' conversion of R1C1:R & FinalRow & C8

' set range for Pivot table placement
Set TableDest = Sheets(DataSheet).Cells(1, 9)  ' conversion of R1C9

Set PvtCache = ActiveWorkbook.PivotCaches.Add(xlDatabase, DataRng)

' this line in case the Pivot table doesn't exit >> first time running this Macro
On Error Resume Next
Set PvtTbl = ActiveWorkbook.Sheets(DataSheet).PivotTables("PivotTable4") ' check if "PivotTable4" Pivot Table already created (in past runs of this Macro)

On Error GoTo 0
If PvtTbl Is Nothing Then ' "PivotTable4" doesn't exist >> create it

    ' create a new Pivot Table in "PivotTable4" sheet
    Set PvtTbl = ActiveWorkbook.Sheets(DataSheet).PivotTables.Add(PivotCache:=PvtCache, TableDestination:=TableDest, TableName:="PivotTable4")

    With PvtTbl.PivotFields("Document Type")
        .Orientation = xlRowField
        .Position = 1
    End With

    With PvtTbl.PivotFields("Accounting Event")
        .Orientation = xlRowField
        .Position = 2
    End With

    With PvtTbl.PivotFields("Document Number")
        .Orientation = xlRowField
        .Position = 3
    End With

    PvtTbl.AddDataField ActiveSheet.PivotTables( _
    "PivotTable4").PivotFields("Amount"), "Sum of Amount", xlSum

Else
    ' just refresh the Pivot cache with the updated Range
    PvtTbl.ChangePivotCache PvtCache
    PvtTbl.RefreshTable
End If


End Sub

Upvotes: 2

Related Questions