VBA to create a pivot table with dynamic source data

SBNUT

New Member
Joined
Aug 25, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
I have a workbook with many work sheets. In worksheet "Job List" I have a macro that builds a dynamic list with the header data in C7:J7. There can be one data row up to several hundred data rows. So I need to be able to copy the source data dynamically to the last row of data. I need to create a pivot table from this data to worksheet "Calc" starting in A1 of that sheet. The row will be "Acct. Code"Also, and the columns will be "Total W/O Tax" and "Total Price". I need to turn ColumnGrand to False so it doesn't create the grand total. I recorded a macro to create the pivot table, however it doesn't work all the time, so I thought VBA would the best answer. I know a little about VBA and can usually work my way through, but his is a little over my head. The following screen prints show 1) The source data 2) what I need the pivot table to look like. Any help would be greatly appreciated.


1632848659412.png


1632848751451.png
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

SBNUT

New Member
Joined
Aug 25, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
This is what I have come up with so far, however there is syntax error (in red below) that I can't see what is wrong with the code.

Sub PivotTable()
'
' PivotTable Macro
'

'
Dim lastRow As Long
Dim lastCol As Long

lastRow = 1
lastCol = 1

While ActiveSheet.Cells(lastRow, lastCol).Value <> ""
lastCol = lastCol + 1
Wend

lastCol = lastCol - 1

While ActiveSheet.Cells(lastRow, 1).Value <> ""
lastRow = lastRow + 1
Wend

lastRow = lastRow - 1
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Job List!R" & lastRow & "C" & lastCol", Version:=7).CreatePivotTable TableDestination:= _

"CalcSheet!R1C1", TableName:="PivotTable23", DefaultVersion:=7
Sheets("CalcSheet").Select
Cells(1, 1).Select
With ActiveSheet.PivotTables("PivotTable23")
.ColumnGrand = False
.HasAutoFormat = True
.DisplayErrorString = False
.DisplayNullString = True
.EnableDrilldown = True
.ErrorString = ""
.MergeLabels = False
.NullString = ""
.PageFieldOrder = 2
.PageFieldWrapCount = 0
.PreserveFormatting = True
.RowGrand = True
.SaveData = True
.PrintTitles = False
.RepeatItemsOnEachPrintedPage = True
.TotalsAnnotation = False
.CompactRowIndent = 1
.InGridDropZones = False
.DisplayFieldCaptions = True
.DisplayMemberPropertyTooltips = False
.DisplayContextTooltips = True
.ShowDrillIndicators = True
.PrintDrillIndicators = False
.AllowMultipleFilters = False
.SortUsingCustomLists = True
.FieldListSortAscending = False
.ShowValuesRow = False
.CalculatedMembersInFilters = False
.RowAxisLayout xlCompactRow
End With
With ActiveSheet.PivotTables("PivotTable23").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("PivotTable23").RepeatAllLabels xlRepeatLabels
With ActiveSheet.PivotTables("PivotTable23").PivotFields("Acct. Code")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable23").AddDataField ActiveSheet.PivotTables( _
"PivotTable23").PivotFields("Total W/O Tax"), "Sum of Total W/O Tax", xlSum
ActiveSheet.PivotTables("PivotTable23").AddDataField ActiveSheet.PivotTables( _
"PivotTable23").PivotFields(" Total Price"), "Sum of Total Price", xlSum
ActiveCell.Offset(0, 1).Columns("A:B").EntireColumn.Select
Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,109
Messages
5,768,148
Members
425,458
Latest member
Jaspal1996

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top