Sub ProjektVertriebGesamt()
strTabName = "PVGANZ"
strPTName = "PT01"
strPTCName = "PTC01"
strQuelle = "Projekte"
strSourceData = "PV_Projekte"
Worksheets("Projekte").Activate
Set pc = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=strSourceData)
Worksheets.Add after:=Worksheets(strQuelle)
ActiveSheet.Name = strTabName
Set pt = pc.CreatePivotTable(TableDestination:=Range("A3"), _
TableName:=strPTName)
With pt
.PivotFields("Monat/Jahr").Orientation = xlRowField
With .PivotFields("Projekt")
.Orientation = xlDataField
.NumberFormat = "0"
.Function = xlCount
.Name = "Anzahl von Projekte"
End With
.PivotFields("Abteilung").Orientation = xlPageField
End With
Set pt = ActiveSheet.PivotTables(strPTName)
Set pf = pt.PivotFields("Abteilung")
For Each pi In pf.PivotItems
Select Case pi.Name
Case "Vertrieb"
pi.Visible = True
Case Else
pi.Visible = False
End Select
Next
ActiveSheet.Range("D1").Value = ActiveSheet.PivotTables(strPTName).Name
Worksheets(strTabName).Activate
lastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
lastCol = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column
Set WerteBereich = Worksheets(strTabName).Range(Cells(1, 1), Cells(lastRow, lastCol))
Set MeinDiagramm = Worksheets(strTabName).Shapes.AddChart2( _
Style:=234, _
XlChartType:=xlLineMarkers, _
Left:=400, _
Top:=10, _
Width:=600, _
Height:=200, _
Newlayout:=True).CHART
ActiveSheet.ChartObjects(1).Name = strPTCName
Worksheets(strTabName).ChartObjects(1).Activate
With ActiveChart
.SetElement (msoElementLegendNone)
.SetElement (msoElementChartTitleNone)
.ShowAllFieldButtons = False
.SetElement (msoElementPrimaryValueGridLinesMajor)
.SetElement (msoElementPrimaryCategoryGridLinesMajor)
End With
ActiveChart.FullSeriesCollection(1).Trendlines.Add _
Type:=xlLinear, _
Forward:=0, _
Backward:=0, _
DisplayEquation:=0, _
DisplayRSquared:=0, _
Name:="Linear (Vertrieb)"
ActiveChart.FullSeriesCollection(1).Trendlines(1).Select
With Selection.Format.Line
.Visible = msoTrue
.DashStyle = msoLineLongDash
.Weight = 1
End With
End Sub