Error in a recorded macro to create Pivot Table

**aadeshgandhi** · 08-16-2012, 01:55 PM

Hello All,
I have recorded a macro to create a pivot table but I get an run-time error '5' when I run the macro. the bit of code that's causing the issue is here

 ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Pivot Table!R1C1:R13C5", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Pivot Table!R5C9", TableName:="PivotTable4", _
        DefaultVersion:=xlPivotTableVersion14

First question is how to make the macro work each time and second question is how to ensure that all rows containing data is selected? in the code above the source data is set at R1C1 to R13C5. I will be having more or less number of rows depending upon the actual use case.

Thanks,
Aadesh

**Kelshaer** · 08-16-2012, 02:46 PM

use this instead of the bit of code you have posted

    Dim oSH As Worksheet
    Set oSH = ActiveWorkbook.Worksheets("Pivot Table")
    Dim r As Long
    r = oSH.UsedRange.Rows.Count
   ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        oSH.Range("A1:E1" & r), Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:=oSH.Range("I5"), TableName:="PivotTable4", _
        DefaultVersion:=xlPivotTableVersion14