Hi Everyone
I need assistance with the following code. I have exactly the same code for 2 other worksheets and it runs perfectly. But with this one i get the above mentioned error in the line highlighted in bold. My source data is essentially 29 columns with headings starting from row 3. As you can see the for each spreadsheet I find the last row which is used to define a range for the pivot table.
I am not actually experienced with creating pivot tables via vba. Is there something I am missing that causes the pivotcache method to fail? Your help would be much appreciated.
Sub CreatePivottable3()
Dim wsData1 As Worksheet
Dim wsPT1 As Worksheet
Dim PT_cache1 As PivotCaches
Dim PT1, PT2, PT3, PT4 As PivotTable
Dim Lastrow1 As Long
Dim Prange1 As Range
Dim objfield1, objfield2, objfield3, objfield4 As PivotField
With ThisWorkbook
Set wsData1 = .Worksheets("JointLifeTLA")
Set wsPT1 = .Worksheets("PivotTableJointLifeTLA")
End With
'determines last row number based on column A
Lastrow1 = wsData1.Cells(Rows.Count, 1).End(xlUp).Row
Set Prange1 = wsData1.Range(wsData1.Cells(3, 1), wsData1.Cells(Lastrow1, 29))
'Creating pivot table cache
Set PT_cache1 = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Prange1, Version:=xlPivotTableVersion15)
'create pivot table
Set PT1 = PT_cache1.CreatePivotTable(TableDestination:=wsPT.Cells(1, 1), TableName:="Pivot table sum of single premium")
Set PT2 = PT_cache1.CreatePivotTable(TableDestination:=wsPT.Cells(40, 1), TableName:="Pivot table average single premium")
Set PT3 = PT_cache1.CreatePivotTable(TableDestination:=wsPT.Cells(80, 1), TableName:="Pivot table Avearage Income")
Bookmarks