Create pivot table in the same worksheet as the source data (Excel VBA)

Question

I have some data which is the source for a pivot table I am building with following VBA script.

The code is creating a new sheet and generating pivot table there but I want the pivot table to be present in the same sheet where source data is.

It also requires to check till where existing data is and have to select an empty cell.

Sub ApplyPivot()

ActiveWorkbook.Sheets("NOIDA").Select
Range("A1").Select
Set objTable = Sheet1.PivotTableWizard

Set objField = objTable.PivotFields("sector")
objField.Orientation = xlRowField

Set objField = objTable.PivotFields("number")
objField.Orientation = xlColumnField

Set objField = objTable.PivotFields("quantity")
objField.Orientation = xlDataField
objField.Function = xlCount
objField.NumberFormat = " ######"

End Sub

Is this your Entire code? Above code doesn't create new sheet. Can you share full code? — Siva

Robin Mackenzie Robin Mackenzie · Accepted Answer · 2016-08-05T12:00:56

Here's a very basic example of how to create a Pivot Table in code based on a Range of source data - it's just a couple of extra lines rather than use the wizard per the code in your question.

In order to place the table clear of your source data it is checking the number of columns in the source data and then adding 2 columns i.e. there will be a single empty column between the source data and the pivot table.

Option Explicit

Sub ApplyPivotOnSameSheet()

    Dim wsTarget As Worksheet
    Dim rngData As Range
    Dim rngPivotTarget As Range
    Dim objCache As PivotCache
    Dim objTable As PivotTable
    Dim objField As PivotField

    'create pivot table in code (no wizard) on same sheet
    Set wsTarget = ThisWorkbook.Sheets("NOIDA")
    'set range to build table from
    Set rngData = wsTarget.Range("A1").CurrentRegion
    'set range for left-hand corner of pivot clear of source data by 1 column
    Set rngPivotTarget = wsTarget.Cells(1, rngData.Columns.Count + 2)
    'create cache from data
    Set objCache = ThisWorkbook.PivotCaches.Create(xlDatabase, rngData)
    'create table from cache
    Set objTable = objCache.CreatePivotTable(rngPivotTarget)

    'your original code
    Set objField = objTable.PivotFields("sector")
    objField.Orientation = xlRowField

    Set objField = objTable.PivotFields("number")
    objField.Orientation = xlColumnField

    Set objField = objTable.PivotFields("quantity")
    objField.Orientation = xlDataField
    objField.Function = xlCount
    objField.NumberFormat = " ######"

End Sub

Create pivot table in the same worksheet as the source data (Excel VBA)

1 Answers