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