How to select range for pivot table?

Question

I created a macro for making a pivot table. The macro, while creating pivot table, refers to a fixed cell range instead of the range selected. I select by going max right and max down. My range has fixed columns but rows vary depending on data.

The macro up to pivot creation:

Sub Pivot_BS()
'
' Pivot_BS Macro
'
' Keyboard Shortcut: Ctrl+Shift+B
'
    
    Application.Goto Reference:="R1C1"
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "R1C1:R80000C7", Version:=6).CreatePivotTable TableDestination:= _
        "R1C10", TableName:="PivotTable2", DefaultVersion:=6
    Cells(1, 10).Select

In the code the sourcedata range is fixed according to range selected while recording a macro. To avoid missing rows, I have taken that range to row 80,000 but this also gives a "Blank" range in pivot report.

How do I create a dynamic reference that is based on the area selected rather than fixed range?

Range selected with the following steps:

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

Andreas Andreas · Accepted Answer · 2020-11-10T16:48:55

You can try this, it sets the range as A1:[LastColumnLastRow]

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "A1:" & Range("A1").Offset(ActiveSheet.CurrentRegion.Rows.Count - 1, ActiveSheet.CurrentRegion.Columns.Count - 1), Version:=6).CreatePivotTable TableDestination:= _
    "R1C10", TableName:="PivotTable2", DefaultVersion:=6

It's untested code since I write this from my phone.

How to select range for pivot table?

1 Answers