Create Range from highlighted cells for Pivot Table Source Data

[athyeh]

Hello,

I am trying to use the following code to highlight all cells w/ data in my worksheet, and then use the highlighted cells as a datasource for my Pivot Table, but can't figure out how to point "SrcData = ..." line to a dynamic range... Please help.

Thanks!

Dim PvtRng As Range

Range("A1").Select

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

PvtRng = ActiveSheet.Selection

SrcData = ActiveSheet.Sheet1 & "!" & PvtRng.Select.Address(ReferenceStyle:=xlR1C1)

[

[Bernie Deitrick]

As long as your heading are in row 1....

    Dim PvtRng As Range
    Dim sht As Worksheet

    Set PvtRng = ActiveSheet.UsedRange

    Set sht = Worksheets.Add
    
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        PvtRng.Address(True, True, xlR1C1, True), Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:=sht.Name & "!R3C1", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion14