How To Create Pivot Table Using VBA

how-to-create-a-pivot-table-using-vbaHow to create a pivot table using VBA? Assuming that the data for the pivot table is laid out like this, then all you need to create a pivot using vba are there four lines of code:

The pivot table so created using the VBA code above can be further modified. You can make a field appear in the row or field columns by using the following codes.

Say we have a field labeled “my_field_name“. The first one move a field to the row position.

The below code will move the field to the column position.

And finally, this one will move a field to the data field.

Read more about reading data from pivot table

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Comments and Trackbacks

  1. Vamsi wrote:

    vba code is very good

  2. Ajay wrote:

    Thanks – Glad you found it useful ๐Ÿ™‚

  3. fredy wrote:

    thanks for code, nice…


  4. Jaconaut wrote:

    How do you prevent it from creating a new sheet for the table?

    I was able to move the table over to an exising sheet, but not make that the original destination….

    Changing the TableDestination:=”” to TableDestination:= Sheets”Weather-O-Matic” fails.

    Heres my code:

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=ActiveSheet.UsedRange).CreatePivotTable TableDestination:=””, TableName:=”WeatherTable”, DefaultVersion:=xlPivotTableVersion10

    ActiveSheet.PivotTableWizard TableDestination:=Sheets(“Weather-O-Matic”).Cells(2, 2)

    ActiveSheet.PivotTables(“WeatherTable”).AddFields RowFields:=”DMA Name”

    ActiveSheet.PivotTables(“WeatherTable”).PivotFields(“DMA Name”).Orientation = xlDataField

    ActiveWorkbook.ShowPivotTableFieldList = False


  5. Sunil wrote:

    While recording pivot table in VB of excel , this will ask to insert a new sheet, as an when i run macro in the same workbook the sheets name changes EX sheet 1,2,3,4, this causes , is there any VB to save the pivot table to already exsisting sheet .

  6. Mohsin wrote:

    Subject::Help Required
    B/m code is fine for 65536 number of rows or less.But when number of rows exceeds 65536, it gives an error”Type Mismatch”.
    Please help in this regard.I am using Excel 2010.

    Sub test()
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ActiveSheet.UsedRange, Version:=xlPivotTableVersion14).CreatePivotTable TableDestination:=””, TableName:=”PivotTable8″, DefaultVersion:=xlPivotTableVersion14
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(1, 1)
    ActiveChart.ChartType = xlColumnClustered
    End Sub

  7. vincenzo landi wrote:

    Hello, i have a macro similar to one in the example. I want to know if it is posible save this macro as to be used in all worksheet
    Thank you

  8. Nilesh maurya wrote:

    I want to learn VBA Coding.

  9. srinivas wrote:

    Good but code should be explained step by step then on beginners will understand properly

  1. Create A Pivot Table In Excel 2010 Using Vba – How ot be in 2016 wrote:

    […] How To Create Pivot Table Using VBA | Excel & VBA โ€“ Databison โ€“ The pivot table so created using the VBA code above can be further modified. You can make a field appear in the row or field columns by using the following codes. […]

  2. Macro to Create Pivot Table on Existing WorkSheet wrote:

    […] document.write(''); I believe this tutorial is all you need to know about creating pivot tables with VBA: How To Create Pivot Table Using VBA | Excel & VBA – Databison […]


Keep up with the latest stories - Delivered right to your inbox


English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel