I often find myself creating pivot tables in Excel manually, which is a cumbersome process – particularly if the source range for the data grows later (you add more rows or columns). The following two VBA macros simplify this process to a large extent.
swaDynamicPivotSourceAdd
creates the named, dynamic rangepivotsource
, which is the active cell and all nonempty rows below and all nonempty columns to the right. In the example below, the active cell is C4 and the macro defines the yellow marked area as the source range. If you add more columns or rows to the source data, the dynamic range grows accordingly (just refresh the pivot table and new data appears).Result of
swaDynamicPivotSourceAdd
swaDynamicPivotAdd
creates the named, dynamic range by calling the macro above. In the second step, the macro adds a worksheet and creates the pivot table. If you want to layout the pivot table, simply insert you own code to"insert own code here"
.
And here is the source code for the two macros
Option Explicit Sub swaDynamicPivotSourceAdd() Dim swaFormula As String Dim swacol, swarow As Integer swarow = ActiveCell.Row - 1 swacol = ActiveCell.Column - 1 ' make it comma and not colon swaFormula = "=OFFSET(" & _ ActiveWorkbook.Worksheets(ActiveSheet.Index).Name & "!R" & ActiveCell.Row & "C" & ActiveCell.Column & ",0,0,COUNTA(" & _ ActiveWorkbook.Worksheets(ActiveSheet.Index).Name & "!C" & ActiveCell.Column & ") -" & swarow & ",COUNTA(" & _ ActiveWorkbook.Worksheets(ActiveSheet.Index).Name & "!R" & ActiveCell.Row & ")-" & swacol & ")" ActiveWorkbook.Names.Add Name:="pivotsource", RefersToR1C1:=swaFormula End Sub ' ' adds a worksheet and lays out a pivot table with range "pivotsource" as data source ' Sub swaDynamicPivotAdd() Dim PTCache As PivotCache Dim pt As PivotTable Dim WS As Worksheet swaDynamicPivotSourceAdd ' define the range which serves as the data range Set WS = Sheets.Add ' add new sheet to hold the pivot table Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="=pivotsource") ' Create the pivot table Set pt = PTCache.CreatePivotTable(TableDestination:=WS.Range("A3"), TableName:="SamplePivot", DefaultVersion:=xlPivotTableVersion10) ' Set update to manual to avoid recomputation while laying out pt.ManualUpdate = True ' lay out data ' insert own code here ' insert own code here ' insert own code here WS.Cells(3, 1).Select ' Now calc the pivot table pt.ManualUpdate = False End Sub
Pingback: Dump actual work from MS Project into Excel | waldherr.org
this looks really good, but i can’t get the macro to work. can you email me a spreadsheet with the macro embedded in it?