Using a variable within Pivot Table Source Data property

[jbunn]

May I request assistance on using variables within a Pivot Table Source Data property. I am receiving an error when using the following code

   'Create Pivot Table off of Consolidated Employee Data
         Dim LR As Long
Dim LC As Long
    LR = Range("A:AO").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    LC = Range("A:AO").Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:="Consolidated Projects!R1C1:R&"LR"C&"LC"", _
        Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="", _
        TableName:="PivotTable9", _
        DefaultVersion _
        :=xlPivotTableVersion14
    
    With ActiveSheet.PivotTables("pivottable9").PivotFields("BST Project Code")
        .Orientation = xlRowField
        .Position = 1
    ActiveSheet.PivotTables("PivotTable9").PivotFields ("Source")
        .Orientation = xlColumnField
        .Position = 1
    End With

 

[RoryA]

Close.

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:="'Consolidated Projects'!R1C1:R" & LR & "C" & LC, _
Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="", _
TableName:="PivotTable9", _
DefaultVersion _
:=xlPivotTableVersion14

 

[jbunn]

That did the trick! Thanks!