How 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
vba code is very good
October 31st, 2009 at 2:30 pmThanks – Glad you found it useful 🙂
November 1st, 2009 at 1:30 amthanks for code, nice…
saludos!!!
April 23rd, 2010 at 8:46 amHow 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
September 23rd, 2010 at 6:25 amWhile 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 .
October 14th, 2010 at 8:19 amSubject::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()
March 10th, 2011 at 1:39 amActiveWorkbook.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
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
April 1st, 2014 at 12:11 pmThank you
I want to learn VBA Coding.
October 14th, 2016 at 5:56 amGood but code should be explained step by step then on beginners will understand properly
March 2nd, 2017 at 1:31 pm