Creating

A pivottable is a buffer where the data is temporarily stored for quick access.
It acts as the intermediate between the data source and the actual pivot table
You can create a pivot cache using the Add method of the PivotCaches collection

You can also use the Pivot caches to generate multiple pivot tables using the same data source

Dim objPivotCache As PivotCache 
Dim objPivotTable As PivotTable 

Set objPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlPivotTableSourceType.xlDatabase, _ 
                                                   SourceData:="Named_Range") 

SourceType - The source of the PivotTable cache data.
SourceData - The data for the new PivotTable cache. This argument is required if SourceType isn't xlExternal. Can be a Range object, an array of ranges, or a text constant that represents the name of an existing PivotTable report. For an external database, this is a two-element array. The first element is the connection string specifying the provider of the data. The second element is the SQL query string used to get the data. If you specify this argument, you must also specify SourceType.
SourceDate - a two or more element array containing information about the data source and SQL query
If the SQL statement is longer than 255 characters then it must be broken up and passed as multiple entries in the array.

sConnection = "ODBC;" & _ 
          "DBQ=" & ThisWorkbook.Path & "\sample.mdb;" & _ 
          "Driver = {Microsoft Access Driver (*.mdb)};" 
sQuery = "SELECT * FROM Table_Name" 
Dim QueryArray As Variant 
Set QueryArray = Array(sConnection,sQuery) 
SourceData :=  QueryArray 

Uses a query stored in the Access Database
sQuery = "SELECT * FROM Query1"

Set objPivotTable = ActiveSheet.PivotTables.Add(PivotCache:=cache, _ 
                                                TableDestination:="" 
                                                TableName:="PivotTable1", _ 
                                                DefaultVersion:=xlPivotTableVersion10) 

PivotCache - The PivotTable cache on which the new PivotTable report is based. The cache provides data for the report.
TableDestination - The cell in the upper-left corner of the PivotTable report's destination range (the range on the worksheet where the resulting report will be placed). You must specify a destination range on the worksheet that contains the PivotTables object specified by expression .
TableName - The name of the new PivotTable report.
ReadData - True to create a PivotTable cache that contains all records from the external database; this cache can be very large. False to enable setting some of the fields as server-based page fields before the data is actually read.
DefaultVersion - The version of Microsoft Excel the PivotTable was originally created in.

You can also create a pivottable using the CreatePivotTable method of the PivotCache object

ActuveWorkbook.PivotCaches.Add( _ 
     SourceType:=xlDatabase, _ 
     SourceData:="Named_Range").CreatePivotTable _ 
            TableDestination:="", _ 
            TableName:="PivotTable1", _ 
            DefaultVersion:=xlPivotTableVersion10 

By default the top left cell of the range is selected after using the CreatePivotTable method

Range("A1").Select 
ACtiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _ 
                                            SourceData:=Sheet1!R1C1:R13C4").CreatePivotTable TableDestination:="", TableName:="PivotTable1" 

With ActiveSheet.PivotTables("PivotTable1") 
   .PivotFields(" ").Orientation = xlPageField 
   .PivotFields(" ").Position:=1 
   .PivotFields(" ").Orientation = xlColumnField 
   .PivotFields(" ").Position:=1 
   .PivotFields(" ").Orientation = xlRowField 
   .PivotFields(" ").Position:=1 
   .PivotFields(" ").Orientation = xlDataField 
   .PivotFields(" ").Position:=1 
End WIth 

This line will display the grand columns total

Activesheet.PivotTables("PivotTable1").ColumnGrand = True 

This line will display the grand row totals

Activesheet.PivotTables("PivotTable1").RowGrand = True