Table Destination (Pivot Table) VBA

jaszz

New Member
Joined
Mar 25, 2019
Messages
5
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.
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Kamolga

Well-known Member
Joined
Jan 28, 2015
Messages
1,176
What about
Code:
[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

New Member
Joined
Mar 25, 2019
Messages
5
What about
Code:
[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:

Code:
    '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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,924
Messages
5,525,661
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top