Excel Macro to create Pivot Table with specifics

Question

this is my first question on here, so I apologize for any confusion.

I recently started a new job that deals with pivot tables quite frequently. I have made a simple shortcut by myself to open the pivot table window to speed up the process slightly, but I am looking to do a bit more with it.

I was trying to record a macro that selected a cell inside the information group given, to create the pivot table from that grouping of information, and place it in a new empty cell in the existing document somewhere below the given grouping of information. I believe this may be more of a coding solution, than a macro one, given that the same cell will not always be blank to place the table, etc.

Is there a way to do this by, say, using the current selected cell to make the table, and then placing it maybe 2-3 cells below the current selection? This way I can click on the bottom cell of the information given, and watch the table populate itself below.

Thanks for any help in advance!

Mitchell

Yes, using VBA... Do you have any code or attempt to perform what you want? This is not a code service site. — user8753746
– user8753746, Commented Oct 26, 2017 at 16:40
you know the alt ,n ,v is a pivottable shortcut when you are inside of the data range? — QHarr
– QHarr, Commented Oct 26, 2017 at 16:46
I do, thank you. I was looking to turn the whole act of doing that, skipping the pop up box, and creating the table in that same document all in one shortcut. — Magresti
– Magresti, Commented Oct 26, 2017 at 18:12

Arjeel · Accepted Answer · 2017-10-26 18:40:04Z

This should suffice: Credit:https://excelchamps.com/blog/vba-to-create-pivot-table/

Sub Test()

    Dim PSheet As Worksheet
    Dim DSheet As Worksheet
    Dim PCache As PivotCache
    Dim PTable As PivotTable
    Dim PRange As Range
    Dim LastRow As Long
    Dim LastCol As Long

    On Error Resume Next
    'Set PSheet = Worksheets("PivotTable")
    Set DSheet = Worksheets("Sheet1")
    Set PSheet = Worksheets("Sheet1")

    'Define Data Range
    LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
    LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

    'Define Pivot Cache
    Set PCache = ActiveWorkbook.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:=PRange). _
    CreatePivotTable(TableDestination:=PSheet.Cells(LastRow + 2, 2), _
    TableName:="SalesPivotTable")

    'Insert Blank Pivot Table
    Set PTable = PCache.CreatePivotTable _
    (TableDestination:=PSheet.Cells(LastRow + 2, 2), TableName:="SalesPivotTable")

    'Insert Row Fields
    With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Year")
    .Orientation = xlRowField
    .Position = 1
    End With
    With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month")
    .Orientation = xlRowField
    .Position = 2
    End With

    'Insert Column Fields
    With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Zone")
    .Orientation = xlColumnField
    .Position = 1
    End With

    'Insert Data Field
    With ActiveSheet.PivotTables("SalesPivotTable")
    .PivotFields ("Amount")
    .Orientation = xlDataField.Position = 1
    .Function = xlSum
    .NumberFormat = "#,##0"
    .Name = "Revenue "
    End With


End Sub

Collectives™ on Stack Overflow

Excel Macro to create Pivot Table with specifics

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related