Need clarification on setting Range in excel from R1c1 to range("a1") type...

[pedie]

When i record a macro it shows...
"Sheet3!R1C1:R49C7"

I want it in like Sheets("Sheet3").Range("A1:E" & lr) etc.

[FONT=Courier New]       ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _[/FONT]
[FONT=Courier New]           PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _[/FONT]
[FONT=Courier New]           "path\[budget pivot table.xls]Sheet3!R1C1:R49C7" _[/FONT]
[FONT=Courier New]           , Version:=xlPivotTableVersion10)[/FONT]

I want to pick range till last row. Please help me out here...
This must be simple but stucked...


Thanks for helping.

I tried like this...

"path\[budget pivot table.xls]Sheet3!" & Range("A1:G49") & , Version:=xlPivotTableVersion10)

but still errors...

 

[Colin Legg]

Hi,

Like this:

"path\[budget pivot table.xls]Sheet3!A1:G49", Version:=xlPivotTableVersion10)

---->

"path\[budget pivot table.xls]Sheet3!A1:G" & LastRow, Version:=xlPivotTableVersion10)

 

[pedie]

Colin, thanks alot but it error...
"Runtime error 1004 - Unable to get the pivot table property of worksheet class"

@

PS: Source if of different workbook

[/FONT]
[FONT=Courier New]ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "E:\Personal Files\Excel Files - Brian\budget pivot table\[budget pivot table.xls]Sheet3!A1:G49", Version:=xlPivotTableVersion10)

Also tried this way

[/FONT]

[FONT=Courier New]Dim LastRow  As Long
LastRow = Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "E:\Personal Files\Excel Files - Brian\budget pivot table\[budget pivot table.xls]Sheet3!A1:G" & LastRow, Version:=xlPivotTableVersion10)

Thanks again and pls make this work for me...

Pedie

 

[Norie]

Pedie

Why not try sticking with the R1C1 notation?

You can still use your last row variable.

strR1C1Range = "R1C1:R" & LastRow & "C7"
 
Dim LastRow  As Long
LastRow = Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "E:\Personal Files\Excel Files - Brian\budget pivot table\[budget pivot table.xls]Sheet3!" & strR1C1Range, Version:=xlPivotTableVersion10)

PS You don't need to use the string, just though it might make things a little clearer.

 

[Colin Legg]

Hi,

The error description suggests to me that the activesheet did not have a pivot table called PivotTable1 when you ran the code...

 

[pedie]

Norie, thanks i tried that too but errors..
Colin thanks again..! you are right the pivot name was incorrect, my bad...

but it still error...
I tried this way,...
Sheets("Sheet3") is of different workbook, do i have to make vba first open the workbook for this?

it is saying i need at least 2 rows of data...
runtime error..(2140724809 - 80070057...)

[/FONT]
[FONT=Courier New]Dim LastRow  As Long
LastRow = Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.PivotTables("Pivot_ped1").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "E:\Personal Files\Excel Files - Brian\budget pivot table\[budget pivot table.xls]Sheet3!A1:G" & LastRow, Version:=xlPivotTableVersion10)
End Sub

if i use rows.count instead of LastRow it picks up till 60000 rows...including blanks which i do not want to...


Thanks again

 

[Colin Legg]

When the error occurs, what value does LastRow hold?

It looks a little strange that you are calculating the last row from one sheet and then applying it to another.

As a test, you could set LastRow to 10 to see if the error persists?

 

[pedie]

It is taking sheet3 in thiswork and not the one in workbook called "budget pivot table.xls"

What should i do now so that it take different book sheet3 for lastrow?
Sorry for wasting your time but thanks alot...

 

[Colin Legg]

You're not wasting my time - this is exactly what the forum is for!

I can help you with that but I need to know a couple of things:

(1) What is the name of the worksheet the pivot table "Pivot_Ped1" sits in?
(2) Is the "budget pivot table.xls" workbook open in the same Excel instance when the code is run?

 

[pedie]

You're not wasting my time - this is exactly what the forum is for!

Thanks again!!!

budget pivot table.xls is not opened...
sheet3 is in budget pivot table.xls which is Source for my pivot.
Pivot_Ped1 sits in "Sheet1" of "Book1.xls"