vba and pivot table | Excel & VBA

Slicer VBA Code – Create, Change or Modify a Pivot Table Slicer using VBA

Here is some VBA code that can help you automate working with a slicer in Excel. Slicers, as you would know, are the latest introduction to Excel. As I mentioned in my previous post, they can also be programattically controlled using simple VBA code. Let's see how. Slicer Creation using VBA - Basic Steps Let's look at the slicer hierarchy - a workbook has slicer caches (accessed the SlicerCaches object), each slicer cache in turn having ...

Pivot Table VBA: VBA to Read & Modify a Pivot Table in Excel

Posted on 11 September 2009

Here is a collection of VBA codes to work with Pivot Tables: 1. Create a pivot table using a VBA Macro 2. Delete all Pivot Tables Using a VBA Macro 3. Refer to a Pivot Table directly within a VBA Macro 4. Refer to a Pivot Table Field directly within a VBA Macro 5. Refer to a Pivot Item of a Pivot Field present in a Pivot Table directly using a VBA Macro 6. Remove old (ghost) Items from ...

Comments (1)

How To Create Pivot Table Using VBA

Posted on 28 June 2009

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: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= ActiveSheet.UsedRange).CreatePivotTable TableDestination:="", TableName:= "PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(1, 1) ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Name" ActiveSheet.PivotTables("PivotTable1").PivotFields("Name").Orientation = xlDataField The pivot table so created using the VBA code above can be ...

Comments (11)

How to Read Data from Pivot Table Using VBA in Excel

Posted on 07 November 2008

Reading pivot tables using vba can be quite easy. Here are a few ways you can do it. You may also want to read my previous article on formatting pivots. (Before you dive further, you may want to download this example which will guide you through some of the steps in reading a pivot table data using vba) The Pivot Table Hierarchy Lets look at this peice of code: Sub ListAllItemObjects()...

Comments (21)