VBA Excel 2013 - Creating a pivot table using a dimensioned range

[DrewB68]

Hi all,

I have written a macro to create pivot tables in a new tab of an Excel 2013 file. The columns (A:AE) will remain static, but the number of rows will vary. For the data source, I would prefer to use a specified range based upon a dimensioned variable named EndRow, something along the lines of "Data Source!("A1:AE" & EndRow)", which works fine for vlookups, etc., but isn't working for pivot tables. I tried dimensioning the range, as well, but haven't been able to figure out the code segment that will run successfully. Note that the last command below ('ActiveWorkbook.PivotCaches.Create...) is the bloated code created by the recorder; I want to replace "Data Source!R1C1:R1048576C31" with a smaller pre-determined range.

    Dim EndRow As Long
    EndRow = Range("F1").End(xlDown).Row
    Dim PvtSrc As Variant
    PvtSrc = Range("A1:AE" & EndRow)
    Sheets("Sheet1").Name = "Data Source"
    Sheets.Add After:=ActiveSheet
    Sheets("Sheet1").Name = "Pivots"
    
[COLOR=#66CC66][/COLOR]    'Create Pivot 1[COLOR=#66CC66]
[/COLOR]    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Data Source!("R1C1:R" & EndRow & "C31"), Version:=xlPivotTableVersion15). _
        CreatePivotTable TableDestination:="Pivots!R5C2", TableName:="PivotTable1" _
        , DefaultVersion:=xlPivotTableVersion15

    'ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Data Source!R1C1:R1048576C31", Version:=xlPivotTableVersion15). _
        CreatePivotTable TableDestination:="Pivots!R5C2", TableName:="PivotTable1" _
        , DefaultVersion:=xlPivotTableVersion15

Thanks for your help!

 

[mart37]

EndRow = Cells(Rows.Count, 1).End(xlUp).Row
SourceData:= "Data Source!A1:AE" & EndRow

When you make from your table an excel-table (format as table) then you can use a dynamic rangename:
SourceData:= "Table1"

 

[DrewB68]

Thank you, sir!