VBA PivotTable Automatic Range-selection

**Brullie** · 06-20-2013, 03:27 AM

Hi

I've recorded a Macro for generating a PivotTable.
By recording the macro Excel took "WERE!R1C1:R20466C14", however I need it to select the actual datarange which can be bigger but also smaller.
How do I adjust it the code so it will select the actual Data Row/Column size?

 ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"WERE!R1C1:R20466C14", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Tabelle2!R3C1", TableName:="PivotTable2", _
DefaultVersion:=xlPivotTableVersion10

Thanks!

**apo** · 06-20-2013, 04:18 AM

Hi..

You need to add some code that will detect the bottom of your range..

Lets say your data is from Column A:J.. starting in row 2.. but the number of rows can change..

In this case.. you need to find the last used row in your data range..

First Dimension your variable..

Dim LastRow as Long

then.. to find the Last Row (assuming you have data in Column A all the way till the bottom)..

'Find Last Row of the Data Range
LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

Then... Your Pivot Table code can be..

 ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Sheets("Sheet1").Range("A2:J" & LastRow), Version:=xlPivotTableVersion10).CreatePivotTable _
        TableDestination:=Sheets("Sheet2").Cells(2, 1), TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion10

Be careful using..

DefaultVersion _
        :=xlPivotTableVersion10

As that will limit the Excel versions your Pivot table will work in.. but if its only for your local machine.. thats fine..

Just noticed you might need to find the last used column...

This is the most reliable thing for that..

Dim rLastCell As Range

Set rLastCell = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)

**JosephP** · 06-20-2013, 05:03 AM

perhaps

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"WERE!" & sheets("WERE").Range("A1").Currentregion.address(referencestyle:=xlr1c1), Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Tabelle2!R3C1", TableName:="PivotTable2", _
DefaultVersion:=xlPivotTableVersion10