Creating Pivot Table with Dynamic Range

Question

Creating a macro that can open multiple files and add a pivot table to each. The files are all formatted the same with the worksheet "details" containing the data needed for the pivot table. However each file has a different amount of rows so I'm trying to use a dynamic range to capture all data on each file.

The coding above this opens the file and formats it as needed to work with the pivot table.

         'Set Dynamic Range

  Dim startCell As String
    Dim lastRow As Long
    Dim lastCol As Long
    Dim WS As String

    WS = "Details"
    Worksheets(WS).Activate

        'Find Last row and column

    lastRow = Cells(Rows.Count, Cells(1, 7).Column).End(xlUp).Row
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column

     Range(Cells(1, 7), Cells(lastRow, lastCol)).Select

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

          Selection.Name = "DynamicRange"

                     Sheets.Add After:=ActiveSheet

        ' Create Pivot Table

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "DynamicRange", Version:=6).CreatePivotTable TableDestination:= _
    "Sheet1!R1C1", TableName:="PivotTable1", DefaultVersion:=6
Sheets("Sheet1").Select
Cells(1, 1).Select
With ActiveSheet.PivotTables("PivotTable1")
    .ColumnGrand = True
    .HasAutoFormat = True

When I get to the "create pivot table" section the first line gives me a run-time error code 1004.

Darrell H Darrell H · Accepted Answer · 2019-02-14T19:17:09

You should avoid using Select and Activate as much as possible. I have modified your code below. You may want to reexamine the range values. You need to define the PivotCache and the PivotTable prior to creating.

Dim startCell As String
Dim lastRow As Long
Dim lastCol As Long
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim PvtCache As PivotCache
Dim PvtTab As PivotTable

Set ws = Sheets("Details")

'Find Last row and column

lastRow = ws.Cells(ws.Rows.Count, 7).End(xlUp).Row
lastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column

ws.Range(ws.Cells(1, 7), ws.Cells(lastRow, lastCol)).Name = "DynamicRange"
Set ws2 = Sheets.Add(After:=ws)
ws2.Name = "PvtTable"

' Create Pivot Table

Set PvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Range("DynamicRange"))

Set PvtTab = PvtCache.CreatePivotTable(ws2.Cells(1, 1), "MyTable")

Creating Pivot Table with Dynamic Range

1 Answers