Pivot Table with Dynamic Range - help please?

[rbis84]

Hi All,

I have a sheet in my workbook called Results. This sheet contains a list of a fixed set of columns (A - N) but with a variable number of rows.

I am trying to create a macro to create a pivot table from this data, which automatically selects all the rows in the Results sheet to form the data set.

I've had a look at some of the other threads on this topic, but just can't get this working. Could someone assist please?

I have the following code that I have pieced together from other threads - you will see I am trying to set "PivotRange" as the data set and include this as the data source in the pivot. I get a subcript out of range error when I run this. Any ideas please?



Dim PivotRange As Range

PivotRange = Range(Worksheets(Results).Cells(1, 1), Cells(lastRow, lastColumn))

Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
PivotRange, Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet5!R3C1", TableName:="PivotTable2", DefaultVersion _
:=xlPivotTableVersion14
Sheets("Sheet5").Select
Cells(3, 1).Select
End Sub

 

[rbis84]

Can anyone assist with this please? I'm stumped!

R