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