How To Create Pivot Table Using VBA




TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

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

TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

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=""> <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…

    saludos!!!

  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

    Sheets(1).Delete

  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)
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.ChartArea.Copy
    Sheets(“Sheet2″).Select
    ActiveSheet.Paste
    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 add.in to be used in all worksheet
    Thank you

Subscribe

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

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel