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]

Okay, you need to open it to get the last row from that sheet.

See if this works for you - it's completely untested. I've added (somewhat pointless) comments to explain what the code is doing.

Sub foo()
    
    Dim wkbSourceData As Workbook
    Dim lngLastRow  As Long
    Dim ptcNew As PivotCache
    Dim rngSourceData As Range
    
    'turn off screen updating so the user doesn't know we opened another workbook
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    'open the other workbook so we can get the last used row
    Set wkbSourceData = Workbooks.Open("E:\Personal Files\Excel Files - Brian\budget pivot table\budget pivot table.xls")
    
    With wkbSourceData.Worksheets("Sheet3")
        lngLastRow = .Range("A" & .Rows.Count).End(xlUp).Row    'get the last used row in Sheet3!A:A
        Set rngSourceData = .Range("A1:G" & lngLastRow)         'now we have a reference to the new pivotcache range
    End With
    
    'create the new pivotcache
    Set ptcNew = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngSourceData, Version:=xlPivotTableVersion10)
    
    'change the pivotcache of the Pivot_ped1 pivot table
    ThisWorkbook.Worksheets("Sheet1").PivotTables("Pivot_ped1").ChangePivotCache ptcNew
    
    wkbSourceData.Close SaveChanges:=False
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
End Sub

 

[pedie]

Collin, great!!! it is perfect...i'll go with this!

I appriciate you help.

Norie, thanks for the idea of adding lastrow vba to R1C1 method...


Thanks again guys!