Table Destination (Pivot Table) VBA

[jaszz]

My pivot table doesn't have a specific cell as its table destination as it will depend on the last used cell. If my last used cell is G1, the table destination of the pivot table should be I1.

Please help. I've been searching through the internet for over 5 hours now and I couldn't find anything.

 

[Kamolga]

What about

[LEFT][COLOR=#222222][FONT=Verdana]Dim Source As Range
Set Source = [COLOR=#FF0000]Sheet1[/COLOR].Range("A1:C8")
Dim Dest As Range
Set Dest =[COLOR=#FF0000] [/COLOR][COLOR=#FF0000]Sheet1[/COLOR].UsedRange.SpecialCells(xlCellTypeLastCell).Offset(0, 2)


[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Source, Version:=6).CreatePivotTable TableDestination:= _
        Dest, TableName:="PivotTable1", DefaultVersion:=6[/FONT][/COLOR][/LEFT]

?

 

[jaszz]

What about

[LEFT][COLOR=#222222][FONT=Verdana]Dim Source As Range
Set Source = [COLOR=#FF0000]Sheet1[/COLOR].Range("A1:C8")
Dim Dest As Range
Set Dest =[COLOR=#FF0000]Sheet1[/COLOR].UsedRange.SpecialCells(xlCellTypeLastCell).Offset(0, 2)


[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Source, Version:=6).CreatePivotTable TableDestination:= _
        Dest, TableName:="PivotTable1", DefaultVersion:=6[/FONT][/COLOR][/LEFT]

?

This is what I currently have:

    'move down thrice    ActiveCell.End(xlDown).Select
    ActiveCell.Offset(3).Select
    ActiveCell.Offset(1).Select


Sheets("Raw DUMP").Select    Range("A1:P1").Select
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A1:P" & LastRow).Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Raw DUMP!R1C1:R1349C16", Version:=6).CreatePivotTable _
        TableDestination:=ActiveCell, TableName:="PivotTable5", _
        DefaultVersion:=6

Sorry I need to do the move down first.