Catch the end row and put it in SourceData

[Joy Liao]

Hey guys!
I'd like to change the data source of "PivotTable3" in the sheet "Mypivot". And the source is "Sheet1".

The original code works as following!
But because every time I have to run the Macro, the end row would be different (the file is downloaded from database), I want to make it catches the end row automatically.
I put code between R & C but seems useless.

Do someone knows how to fix it? Thank you so much!!

------------------------------------------------------------------------------
'The original One(it works)
Sheets("Mypivot").Select
ActiveSheet.PivotTables("PivotTable3").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R3C1:R33537C38", Version:=6)
------------------------------------------------------------------------------
'After I Edited (and this one failed)
Range("D3").Select
rowend = Val(ActiveCell.End(xlDown).Row)

Sheets("Mypivot").Select
ActiveSheet.PivotTables("PivotTable3").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R3C1:R(rowend)C38", Version:=6)

 

[RoryA]

You need to concatenate the value in:

Sheets("Mypivot").PivotTables("PivotTable3").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R3C1:R" & rowend & "C38", Version:=6)

 

[Joy Liao]

You need to concatenate the value in:

Sheets("Mypivot").PivotTables("PivotTable3").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R3C1:R" & rowend & "C38", Version:=6)

Amazing! It works great. Thank you for your helping hand! I appreciate that!