Forum Discussion
null null
Mar 26, 2018Copper Contributor
VBA: Limit to Rows for a Pivot Table
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")
- JKPieterseSilver ContributorIs your workbook by any chance saved as filetype .xls?
- null nullCopper Contributor
It's not. It's saved as a .csv when I run the macro.
Does it need to be saved as .xls or .xlsx first?
- JKPieterseSilver ContributorTBH I don't know. Instead of just opening the CSV files you could use Data, From Text to import a CSV file into an existing sheet. Then you can click Data, Refresh All to have Excel ask for another CSV, it'll update the data for you and update the pivottable. Then save-as the file to a new name to keep the current CSV/pivottable combination.