Excel VBA Macro Create Pivot Table with Dynamic Data Range: Source Data Range Error

Question

I am new to VBA coding and trying to create an Excel macro which will create a pivot table based on a dynamic data range. I am using Excel 2016.

The number of columns in the data will remain the same but the number of rows is dynamic.

In my data worksheet, column BZ contains a count of how many records are in the dataset. E.g. If there are 30 records in the dataset, every value in column BZ will be 30. (This data is imported from SAS using DDE which is why I have chosen this method). I have therefore added 1 onto the lastRow variable below to include column names.

My VBA Macro code is currently as follows:

Sub Macro1()
   Dim xlsPath As String
   Dim xlsFile As String
   Dim lastRow As Integer
   Dim SrcData As String
   Dim StartPvt As String
   Dim pvtCache As PivotCache
   Dim pvt As PivotTable

   xlsxPath = "N:\Analytics\Test\DDE\"
   xlsxFile = "Test.xlsx"

   lastRow = Sheets(1).Range("BZ2") + 1

   SrcData = ActiveSheet.Name & "!" & Range("A1:R" & lastRow & "C77").Address(ReferenceStyle:=xlR1C1)

   StartPvt = sht.Name & "!" & sht.Range("A3").Address(ReferenceStyle:=xlR1C1)

   Workbooks.Open Filename:=xlsxPath & xlsxFile

   Range("A1:BY" & lastRow).Select

   Sheets.Add

   Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
   SourceType:=xlDatabase, _
   SourceData:=SrcData, Version:=6)

   Set pvt = pvtCache.CreatePivotTable( _
   TableDestination:=StartPvt, _
   TableName:="PivotTable1", DefaultVersion:=6)

   Columns("BZ:BZ").Select
   Selection.Delete Shift:=xlToLeft
   ActiveWorkbook.Save
   ActiveWorkbook.Close
End Sub

This produces an error which says:

Run-time error '1004':

Method 'Range' of 'object'_Global' failed

The debugger points to the line where the SrcData range is determined. I think the issue may be syntax, where I have specified the lastRow within Range.

A solution for this line of code, or any other helpful comments would be greatly appreciated.

Answer 1

You can turn on the Macro Recorder, then click anywhere in your dataset and Excel will automatically select the last used column and last used row in that dataset, just before creating a Pivot Table. Try it and you will see. If you want to use VBA to dynamically select the last row and dynamically select the last column, and check out the code below.

Ways To Find The Last Row

Sub FindingLastRow()
'PURPOSE: Different ways to find the last row number of a range
'SOURCE: www.TheSpreadsheetGuru.com

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet

'Using Find Function (Provided by Bob Ulmas)
  LastRow = sht.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

'Using SpecialCells Function
  LastRow = sht.Cells.SpecialCells(xlCellTypeLastCell).Row

'Ctrl + Shift + End
  LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

'Using UsedRange
  sht.UsedRange 'Refresh UsedRange
  LastRow = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row

'Using Table Range
  LastRow = sht.ListObjects("Table1").Range.Rows.Count

'Using Named Range
  LastRow = sht.Range("MyNamedRange").Rows.Count

'Ctrl + Shift + Down (Range should be first cell in data set)
  LastRow = sht.Range("A1").CurrentRegion.Rows.Count

End Sub

Ways To Find The Last Column:

Sub FindingLastColumn()

'PURPOSE: Different ways to find the last column number of a range
'SOURCE: www.TheSpreadsheetGuru.com

Dim sht As Worksheet
Dim LastColumn As Long

Set sht = ThisWorkbook.Worksheets("Sheet1")

'Ctrl + Shift + End
  LastColumn = sht.Cells(7, sht.Columns.Count).End(xlToLeft).Column

'Using UsedRange
  sht.UsedRange 'Refresh UsedRange
  LastColumn = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column

'Using Table Range
  LastColumn = sht.ListObjects("Table1").Range.Columns.Count

'Using Named Range
  LastColumn = sht.Range("MyNamedRange").Columns.Count

'Ctrl + Shift + Right (Range should be first cell in data set)
  LastColumn = sht.Range("A1").CurrentRegion.Columns.Count

End Sub

You can easily incorporate this into the code you already have.