VBA to Select Dynamic Pivot Table Data Source

Create a pivot table that is based on a dynamic data source -- that is a range that adjusts automatically, if data is added or removed. Below codes are able to create a dynamic pivot table that will work on data that varies in the number of rows or columns.

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Sheet1!" & Sheets("Sheet1").Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1), Version:=6).CreatePivotTable TableDestination:="", TableName:="PivotTable1", DefaultVersion:=6

Alternatively, a dynamic range is a good solution for excel without vba.

1. Create a new Name Manager to define the range. Basic rule for name manager is no space. In this case, my name is "all_data'
2. In the 'Refers to' box, use the following formula, adapted for your own data:

OFFSET(OSR!$A$1,0,0,COUNTA(OSR!$A:$A),11)

Where:

OSR is worksheet name of the pivot data.
Range $A$1 is  the first cell in the header row.
$A:$A is count the number of non-blank cells in column A - change this to a column that will always have an entry for each row.
11 is the number of columns across your data is - e.g. if you have columns A to K filled then this number would be 11.