VBA -Place Pivot Table in Same worksheet

Question

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

I believe your trouble is coming from this line: ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ DataSheet & "!R1C1:R" & FinalRow & "C8", Version:=xlPivotTableVersion15). _ CreatePivotTable TableDestination:=DataSheet & "!R1C9", TableName:= _ "PivotTable4", DefaultVersion:=xlPivotTableVersion15 Sheets(DataSheet).Select - Why are you trying to select it? I think the format of the TableDestination is off. — BruceWayne
I am trying to select the an ambiguous data set each time. Each worksheet has a different row length. So I thought the stated above would in some sense select all data on the active workbook. — Cjamros
I mean, that line creates a pivot table, but in the destination, you're trying to .Select something? Does that line work for you at all, I'd think it'd throw an error. I recommend instead, first creating a Range variable to store the destination, then just use that in the TableDestination:= part (i.e. TableDestination := myDest) — BruceWayne
I see what you are saying. You are right, that is the line that I am having an issue with. I expected that it would be an error with the TableDestination:= I am working on adding a range right now to see if that resolves my issue. — Cjamros
(Also, not sure if it happened when pasting to SO, but you have some errant spaces in there as well (...PivotFields("Document Type") .Orientation has a space, for instance). — BruceWayne

Shai Rado Shai Rado · Accepted Answer · 2016-08-24T18:46:53

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

VBA -Place Pivot Table in Same worksheet

1 Answers