Pivot Table-Change rows of data source range

[RaunLGoode]

I am trying to automate some pivot tables. there are 5 Pivot Tables on 5 different worksheets that use a table on worksheet "Data".
The column count will always be 7 (A-G) but the rows will change. How would I define the range of the data source?
This is what I was trying

[Code Starts]
Dim ReportWB As String 'workbook name
Dim FolderPath As String ' Path to workbook
Dim Count5 as Double 'Last row of Data table
...
'Update Pivot Tables

Sheets("Data").Select
Count5 = Cells(Rows.Count, 1).End(xlUp).Row


ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
FolderPath & "\[" & ReportWB & "]Data!R1C1:R" & Count5, _
Version:=xlPivotTableVersion14)

[Code ends]

This returns an error statement telling me the pivot table field is not valid...

As always, I appreciate everyone's help

 

[Kiwiman]

Howzit

Do you also need to specify the closing column?

Something like

FolderPath & "\[" & ReportWB & "]Data!R1C1:R" & Count5 & "C7", _

 

[RaunLGoode]

Thanks Les,
That worked like a charm

RG

 

[Kiwiman]

Howzit

Outstanding and glad to help