Pivot tabel i VBA
Hej. Er igang med en opgave hvor jeg skal lave en Pivot Tabel i VBA. Dernæst skal jeg lave en graf udfra denne pivot tabel. Jeg har begge koder, og de virkede tideligere i dag. Nu har jeg et problem med koden til Pivot tabellen, som lyser gult inde i VBA.- Jeg har skrevet med CAPS-Lock under koden.
Sub AutoPivot1()
Dim PvtTbl As PivotTable
Dim PvtCache As PivotCache
Dim PvtTblName As String
Dim pivotTableWs As Worksheet
PvtTblName = "pivotTableName"
' set the worksheet object where we will create the Pivot-Table
Set pivotTableWs = Sheets("Delayed Students")
' set the Pivot Cache (the Range is static)
Set PvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Delayed Students!R1C1:R1532C7")
' create a new Pivot Table in the new created sheet
Set PvtTbl = pivotTableWs.PivotTables.Add(PivotCache:=PvtCache, TableDestination:=pivotTableWs.Range("J1"), TableName:=PvtTblName)
- DET ER DENNE KODE FOROVEN.
' after we set the PvtTbl object, we can easily modifty all it's properties
With PvtTbl
.ColumnGrand = True
.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
With .PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
.RepeatAllLabels xlRepeatLabels
With .PivotFields("STUDYBOARD_ID")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("FACULTY_ID")
.Orientation = xlDataField
.Position = 1
End With
End With
End Sub