Coding a dynamic data range for a pivot table

[JEMC2]

I am trying to code a dynamic range for a pivot table. I have tried

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=Sheets("FMRTReport.rdl").Range("$A$2:$V$" & LastRow), Version:=xlPivotTableVersion10). _
CreatePivotTable TableDestination:="ActiveCharts!R1C1", TableName:="PivotTable2" _
, DefaultVersion:=xlPivotTableVersion10

where LastRow is a String variable initialized with

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

I have also tried creating a variable defined as a range and substituting it in the formula. Nothing seems to be working. Can anyone help me? I am a basic user, so the easier the solution the better.

Thanks so much!!!

[Norie]

How is it not working?

Errors? Nothing happening? Data not right?

By the way, are you sure you are looking for the last row in column A on the right sheet?

[JEMC2]

I get a Run-time error '13': Type mismatch. When I look at the value for LastRow it is set to the appropriate value based on the data in my spreadsheet.

[Norie]

Doh, missed the 'String variable' bit.

LastRow should be declared as Long (for Long Integer).

[MarvinP]

Hi Jemc and welcome to the forum,

Have you read about Dynamic Named Ranges and how to use them?
http://www.ozgrid.com/Excel/DynamicRanges.htm
http://www.bettersolutions.com/excel...G820716330.htm

Here is an old example I have to show how to do a DNR with Pivot Refresh.
Change Event Refreshes Pivot Table with DNR.xlsm

[JEMC2]

Sorry, Norie. Tried changing it to Long and it still didn't work.

MarvinP, no I haven't read about them. Thanks!