2 Pivot tabel i et excel ark
HejJeg har en kode til en pivot tabel. Jeg skal nu lave have endnu en pivot tabel i det samme ark, men bare ved siden af det gamle. Problemet er så, at min kode gør sådan, at den skal slette arket først og så oprette et nyt ark. Derfor har jeg problemer med at indsætte 2 pivot tabeller i et ark. En der kan hjælpe?
Her er min kode til den første pivot tabel. Nummer 2 skal være det samme, men skal bare placeres ved siden af.
Sub Opgave_3()
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PTable2 As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Statistics").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "Statistics"
Application.DisplayAlerts = True
Set PSheet = Worksheets("Statistics")
Set DSheet = Worksheets("Base")
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)
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="StuderendeFordelingFakultetPivotTable")
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="StuderendeFordelingFakultetPivotTable")
With ActiveSheet.PivotTables("StuderendeFordelingFakultetPivotTable").PivotFields("Faculty_ID")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("StuderendeFordelingFakultetPivotTable").PivotFields("PROGRAM_TYPE_NAME")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("StuderendeFordelingFakultetPivotTable").AddDataField _
ActiveSheet.PivotTables("StuderendeFordelingFakultetPivotTable").PivotFields( _
"STUDENT_ID"), "Count of STUDENT_ID", xlCount
ActiveSheet.PivotTables("StuderendeFordelingFakultetPivotTable").ShowTableStyleRowStripes = True
ActiveSheet.PivotTables("StuderendeFordelingFakultetPivotTable").TableStyle2 = "PivotStyleMedium9"
End Sub