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.

1. swaDynamicPivotSourceAdd creates the named, dynamic range pivotsource, 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

2. 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