VBA and Last Row for Pivot Table

[mihand]

I create monthly reports that vary in the number of rows. What code that should be used in VBA to show the last row for a pivot table? Below is what is existing. It is looking for data in the area of; Access!$A$1:$AJ$19280. Again, each month the row number, 19280, changes. It goes from A to AJ.

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Access!R1C1:R19280C36", Version:=6).CreatePivotTable TableDestination:= _
"Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion:=6

 

[Anthony47]

Add these lines before your code:

pvtSh = "Access"
LastA = Sheets(datash).Cells(Rows.Count, "A").End(xlUp).row
nwRange = datash & "!" & Sheets(datash).Range("A1:AJ1").Resize(LastA).Address(ReferenceStyle:=xlR1C1)

Then, rather than using
SourceData:= _
"Access!R1C1:R19280C36"; Version:=etc etc
use

SourceData:= _
nwRange; Version:=etc etc

Bye

 

[mihand]

It did not work. This is what I edited to;

pvtSh = "Access"
LastA = Sheets(datash).Cells(Rows.Count, "A").End(xlUp).Row
nwRange = datash & "!" & Sheets(datash).Range("A1:AJ1").Resize(LastA).Address(ReferenceStyle:=xlR1C1)
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
nwRange;, Version:=6).CreatePivotTable TableDestination:= _
"Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion:=6

 

[mihand]

The first code did not produce an error in VBA. The second did.

 

[Anthony47]

Sorry, I was in error!
Wrong: pvtSh = "Access"
Il should be:

 datash = "Access"

Try again...

 

[mihand]

It says there is a syntax error for this;

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
nwRange; Version:=6).CreatePivotTable TableDestination:= _
"Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion:=6

 

[Anthony47]

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
nwRange; Version:=6).CreatePivotTable TableDestination:= _
"Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion:=6

The problem is with the semicolon in red; it had to be a Comma