I'm working on a macro which will run across several .csv files. The goal is to take a .csv file, create a new blank worksheet, and add a Pivot Table using the .csv data as the source. The .csv files contain a variable number of rows, but always 8 columns. In testing some of the .csv files, the macro has run successfully with up to 54,000 rows of data. But I've run into an issue at 72,000 rows, where the Pivot Tables are not created. (The threshold may be lower, just haven't tested an example >54K & <72K). Below is the initial section of code.
Is there a way to edit the 'Define Data Range' section to capture the maximum number of rows? I'm able to create the Pivot Tables manually but that defeats the purpose.
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long
' Update Worksheet Data Name
ActiveSheet.Select
ActiveSheet.Name = "Data"
' Insert a New Blank Worksheet - COMM TRx National Accounts Month-Over-Month
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("COMM Rx-Month over Month").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "COMM Rx-Month over Month"
Application.DisplayAlerts = True
Set PSheet = Worksheets("COMM Rx-Month over Month")
Set DSheet = Worksheets("Data")
' Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)
' Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="TRx Data")
