Hohe Auslastung des Arbeitsspeichers
20.08.2019 18:45:40
Simon
wenn ich mein Makro über den "Berechnung Starten" Button starte, dann dauert im Allgemeinen die Berechnung der Zellen und das Kopieren der Daten in andere Tabellenblätter sehr lange. Zudem ist der Arbeitsspeicher mit drei GB belastet.
Ich habe bereits Application.ScreenUpdating = False angewendet.
Leider muss ich immer mit .Select arbeiten, da ich keine funktionierende alternative gefunden habe.
Da noch bis zu 70.000 Positionen in Tabelle1 dazukommen, die sortiert und kopiert werden müssen und ich aktuell bei 2000 Positionen schon massive Geschwindigkeits- und Auslastungsprobleme habe, brauche ich eure Hilfe.
Leider kann ich die Datei aufgrund der Größe nicht hochladen.
Hier mein Code:
Option Explicit
Private Sub Button_Start_Click()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Range("A1").Value = "Halle"
Range("B1").Value = "Anlage"
Range("C1").Value = "Ausfall"
Range("D1").Value = "Start_Datum"
Range("E1").Value = "Ende_Datum"
Range("F1").Value = "In_Start_Datum"
Range("G1").Value = "In_Ende_Datum"
Range("H1").Value = "Text_Stoerung"
Range("I1").Value = "UC_Text"
Range("J1").Value = "UC_Code"
Range("K1").Value = "Baugruppe"
Range("L1").Value = "Anlage_Datum"
Range("M1").Value = "Aenderungs_Datum"
Range("N1").Value = "Bemerkung"
Columns("O:Z").ColumnWidth = 27 ' hier die Tabellen breiter machen
Worksheets("Tabelle1").Select
Dim ivariable As Long
ivariable = 2
Do
'##################################################################################'--------- _
MTTR--------'################################################################################### _
_
'UC-10# NOR-Dia 1
Range("O1").Value = "MTTR/UC-10#NOR"
If Range("J" & ivariable).Text = "10" Then
Range("O" & ivariable).Value = (Range("E" & ivariable).Value - Range("D" & ivariable).Value) * _
_
_
24
End If
'Hier muss der Kopiervorgang und das ablegen in ein weiteres Arbeitsblatt erfolgen.
'UC-1B,1N, 1T# NOR- Dia 2
Range("P1").Value = "MTTR/UC-1B, 1N, 1T#NOR"
If Range("J" & ivariable).Text = "1B" Or Range("J" & ivariable).Text = "1N" Or Range("J" & _
ivariable).Text = "1T" Then
Range("P" & ivariable).Value = (Range("E" & ivariable).Value - Range("D" & ivariable).Value) * _
_
_
24
End If
'UC-10# PONT- Dia 3
Range("Q1").Value = "MTTR/UC-10#PONT"
If Range("J" & ivariable).Value = "10" And Range("A" & ivariable).Value = "NOR Halle 032" Or _
Range("J" & ivariable).Value = "10" And Range("A" & ivariable).Value = "NOR Halle 042" Or Range( _
_
"J" & ivariable).Value = "10" And Range("A" & ivariable).Value = "NOR Halle 056" Or Range("J" & _
ivariable).Value = "10" And Range("A" & ivariable).Value = "NOR Halle 107" Or Range("J" & ivariable).Value = "10" And Range("A" & ivariable).Value = "NOR Halle 180" Or Range("J" & ivariable).Value = "10" And Range("A" & ivariable).Value = "NOR Halle 200" Or Range("J" & ivariable).Value = "10" And Range("A" & ivariable).Value = "NOR Halle 201" Or Range("J" & ivariable).Value = "10" And Range("A" & ivariable).Value = "NOR Halle 202" Or Range("J" & ivariable).Value = "10" And Range("A" & ivariable).Value = "NOR Halle 203" Or Range("J" & ivariable).Value = "10" And Range("A" & ivariable).Value = "NOR Halle 204" Or Range("J" & ivariable).Value = "10" And Range("A" & ivariable).Value = "NOR Halle 205" Then
Range("Q" & ivariable).Value = (Range("E" & ivariable).Value - Range("D" & ivariable).Value) * _
_
_
24
End If
If Range("J" & ivariable).Value = "10" And Range("A" & ivariable).Value = "NOR Halle 206" Or _
Range("J" & ivariable).Value = "10" And Range("A" & ivariable).Value = "NOR Halle 210" Then
Range("Q" & ivariable).Value = (Range("E" & ivariable).Value - Range("D" & ivariable).Value) * _
_
_
24
End If
'UC-1B,1N, 1T# PONT-Dia 4
'1B
Range("R1").Value = "MTTR/UC-1B,1N,1T#PONT"
If Range("J" & ivariable).Value = "1B" And Range("A" & ivariable).Value = "NOR Halle 032" Or _
Range("J" & ivariable).Value = "1B" And Range("A" & ivariable).Value = "NOR Halle 042" Or Range( _
_
"J" & ivariable).Value = "1B" And Range("A" & ivariable).Value = "NOR Halle 056" Or Range("J" & _
ivariable).Value = "1B" And Range("A" & ivariable).Value = "NOR Halle 107" Or Range("J" & ivariable).Value = "1B" And Range("A" & ivariable).Value = "NOR Halle 180" Or Range("J" & ivariable).Value = "1B" And Range("A" & ivariable).Value = "NOR Halle 200" Or Range("J" & ivariable).Value = "1B" And Range("A" & ivariable).Value = "NOR Halle 201" Or Range("J" & ivariable).Value = "1B" And Range("A" & ivariable).Value = "NOR Halle 202" Or Range("J" & ivariable).Value = "1B" And Range("A" & ivariable).Value = "NOR Halle 203" Or Range("J" & ivariable).Value = "1B" And Range("A" & ivariable).Value = "NOR Halle 204" Or Range("J" & ivariable).Value = "1B" And Range("A" & ivariable).Value = "NOR Halle 205" Then
Range("R" & ivariable).Value = (Range("E" & ivariable).Value - Range("D" & ivariable).Value) * _
_
_
24
End If
If Range("J" & ivariable).Value = "1B" And Range("A" & ivariable).Value = "NOR Halle 206" Or _
Range("J" & ivariable).Value = "1B" And Range("A" & ivariable).Value = "NOR Halle 210" Then
Range("R" & ivariable).Value = (Range("E" & ivariable).Value - Range("D" & ivariable).Value) * _
_
_
24
End If
'UC-1B,1N, 1T# PONT
'1N
If Range("J" & ivariable).Value = "1N" And Range("A" & ivariable).Value = "NOR Halle 032" Or _
Range("J" & ivariable).Value = "1N" And Range("A" & ivariable).Value = "NOR Halle 042" Or Range( _
_
"J" & ivariable).Value = "1N" And Range("A" & ivariable).Value = "NOR Halle 056" Or Range("J" & _
ivariable).Value = "1N" And Range("A" & ivariable).Value = "NOR Halle 107" Or Range("J" & ivariable).Value = "1N" And Range("A" & ivariable).Value = "NOR Halle 180" Or Range("J" & ivariable).Value = "1N" And Range("A" & ivariable).Value = "NOR Halle 200" Or Range("J" & ivariable).Value = "1N" And Range("A" & ivariable).Value = "NOR Halle 201" Or Range("J" & ivariable).Value = "1N" And Range("A" & ivariable).Value = "NOR Halle 202" Or Range("J" & ivariable).Value = "1N" And Range("A" & ivariable).Value = "NOR Halle 203" Or Range("J" & ivariable).Value = "1N" And Range("A" & ivariable).Value = "NOR Halle 204" Or Range("J" & ivariable).Value = "1N" And Range("A" & ivariable).Value = "NOR Halle 205" Then
Range("R" & ivariable).Value = (Range("E" & ivariable).Value - Range("D" & ivariable).Value) * _
_
_
24
End If
If Range("J" & ivariable).Value = "1N" And Range("A" & ivariable).Value = "NOR Halle 206" Or _
Range("J" & ivariable).Value = "1N" And Range("A" & ivariable).Value = "NOR Halle 210" Then
Range("R" & ivariable).Value = (Range("E" & ivariable).Value - Range("D" & ivariable).Value) * _
_
_
24
End If
'UC-1B,1N, 1T# PONT
'1T
If Range("J" & ivariable).Value = "1T" And Range("A" & ivariable).Value = "NOR Halle 032" Or _
Range("J" & ivariable).Value = "1T" And Range("A" & ivariable).Value = "NOR Halle 042" Or Range( _
_
"J" & ivariable).Value = "1T" And Range("A" & ivariable).Value = "NOR Halle 056" Or Range("J" & _
ivariable).Value = "1T" And Range("A" & ivariable).Value = "NOR Halle 107" Or Range("J" & ivariable).Value = "1T" And Range("A" & ivariable).Value = "NOR Halle 180" Or Range("J" & ivariable).Value = "1T" And Range("A" & ivariable).Value = "NOR Halle 200" Or Range("J" & ivariable).Value = "1T" And Range("A" & ivariable).Value = "NOR Halle 201" Or Range("J" & ivariable).Value = "1T" And Range("A" & ivariable).Value = "NOR Halle 202" Or Range("J" & ivariable).Value = "1T" And Range("A" & ivariable).Value = "NOR Halle 203" Or Range("J" & ivariable).Value = "1T" And Range("A" & ivariable).Value = "NOR Halle 204" Or Range("J" & ivariable).Value = "1T" And Range("A" & ivariable).Value = "NOR Halle 205" Then
Range("R" & ivariable).Value = (Range("E" & ivariable).Value - Range("D" & ivariable).Value) * _
_
_
24
End If
If Range("J" & ivariable).Value = "1T" And Range("A" & ivariable).Value = "NOR Halle 206" Or _
Range("J" & ivariable).Value = "1T" And Range("A" & ivariable).Value = "NOR Halle 210" Then
Range("R" & ivariable).Value = (Range("E" & ivariable).Value - Range("D" & ivariable).Value) * _
_
_
24
End If
'UC-10# PONM- Dia 5
Range("S1").Value = "MTTR/UC-10#PONM"
If Range("J" & ivariable).Value = "10" And Range("A" & ivariable).Value = "NOR Halle 010" Or _
Range("J" & ivariable).Value = "10" And Range("A" & ivariable).Value = "NOR Halle 102" Or Range( _
_
"J" & ivariable).Value = "10" And Range("A" & ivariable).Value = "NOR Halle 128" Or Range("J" & _
ivariable).Value = "10" And Range("A" & ivariable).Value = "NOR Halle 129" Or Range("J" & ivariable).Value = "10" And Range("A" & ivariable).Value = "NOR Halle 180" Or Range("J" & ivariable).Value = "10" And Range("A" & ivariable).Value = "NOR Halle 380" Then
Range("S" & ivariable).Value = (Range("E" & ivariable).Value - Range("D" & ivariable).Value) * _
_
_
24
End If
'UC-1B,1N, 1T# PONM- Dia 6
'1B
Range("T1").Value = "MTTR/UC-1B,1N,1T#PONM"
If Range("J" & ivariable).Value = "1B" And Range("A" & ivariable).Value = "NOR Halle 010" Or _
Range("J" & ivariable).Value = "1B" And Range("A" & ivariable).Value = "NOR Halle 102" Or Range( _
_
"J" & ivariable).Value = "1B" And Range("A" & ivariable).Value = "NOR Halle 128" Or Range("J" & _
ivariable).Value = "1B" And Range("A" & ivariable).Value = "NOR Halle 129" Or Range("J" & ivariable).Value = "1B" And Range("A" & ivariable).Value = "NOR Halle 180" Or Range("J" & ivariable).Value = "1B" And Range("A" & ivariable).Value = "NOR Halle 380" Then
Range("T" & ivariable).Value = (Range("E" & ivariable).Value - Range("D" & ivariable).Value) * _
_
_
24
End If
'UC-1B,1N, 1T# PONM
'1N
If Range("J" & ivariable).Value = "1N" And Range("A" & ivariable).Value = "NOR Halle 010" Or _
Range("J" & ivariable).Value = "1N" And Range("A" & ivariable).Value = "NOR Halle 102" Or Range( _
_
"J" & ivariable).Value = "1N" And Range("A" & ivariable).Value = "NOR Halle 128" Or Range("J" & _
ivariable).Value = "1N" And Range("A" & ivariable).Value = "NOR Halle 129" Or Range("J" & ivariable).Value = "1N" And Range("A" & ivariable).Value = "NOR Halle 180" Or Range("J" & ivariable).Value = "1N" And Range("A" & ivariable).Value = "NOR Halle 380" Then
Range("T" & ivariable).Value = (Range("E" & ivariable).Value - Range("D" & ivariable).Value) * _
_
_
24
End If
'UC-1B,1N, 1T# PONM
'1T
If Range("J" & ivariable).Value = "1T" And Range("A" & ivariable).Value = "NOR Halle 010" Or _
Range("J" & ivariable).Value = "1T" And Range("A" & ivariable).Value = "NOR Halle 102" Or Range( _
_
"J" & ivariable).Value = "1T" And Range("A" & ivariable).Value = "NOR Halle 128" Or Range("J" & _
ivariable).Value = "1T" And Range("A" & ivariable).Value = "NOR Halle 129" Or Range("J" & ivariable).Value = "1T" And Range("A" & ivariable).Value = "NOR Halle 180" Or Range("J" & ivariable).Value = "1T" And Range("A" & ivariable).Value = "NOR Halle 380" Then
Range("T" & ivariable).Value = (Range("E" & ivariable).Value - Range("D" & ivariable).Value) * _
_
_
24
End If
'UC-10# PONC - Dia 7
Range("U1").Value = "MTTR/UC-10#PONC"
If Range("J" & ivariable).Value = "10" And Range("A" & ivariable).Value = "NOR Halle 410" Or _
Range("J" & ivariable).Value = "10" And Range("A" & ivariable).Value = "NOR Halle 420" Or Range( _
_
"J" & ivariable).Value = "10" And Range("A" & ivariable).Value = "NOR Halle 430" Then
Range("U" & ivariable).Value = (Range("E" & ivariable).Value - Range("D" & ivariable).Value) * _
_
_
24
End If
'UC-1B,1N, 1T# PONC - Dia 8
'1B
Range("V1").Value = "MTTR/UC-1B,1N,1T#PONC"
If Range("J" & ivariable).Value = "1B" And Range("A" & ivariable).Value = "NOR Halle 410" Or _
Range("J" & ivariable).Value = "1B" And Range("A" & ivariable).Value = "NOR Halle 420" Or Range( _
_
"J" & ivariable).Value = "1B" And Range("A" & ivariable).Value = "NOR Halle 430" Then
Range("V" & ivariable).Value = (Range("E" & ivariable).Value - Range("D" & ivariable).Value) * _
_
_
24
End If
'UC-1B,1N, 1T# PONC
'1N
If Range("J" & ivariable).Value = "1N" And Range("A" & ivariable).Value = "NOR Halle 410" Or _
Range("J" & ivariable).Value = "1N" And Range("A" & ivariable).Value = "NOR Halle 420" Or Range( _
_
"J" & ivariable).Value = "1N" And Range("A" & ivariable).Value = "NOR Halle 430" Then
Range("V" & ivariable).Value = (Range("E" & ivariable).Value - Range("D" & ivariable).Value) * _
_
_
24
End If
'UC-1B,1N, 1T# PONC
'1T
If Range("J" & ivariable).Value = "1T" And Range("A" & ivariable).Value = "NOR Halle 410" Or _
Range("J" & ivariable).Value = "1T" And Range("A" & ivariable).Value = "NOR Halle 420" Or Range( _
_
"J" & ivariable).Value = "1T" And Range("A" & ivariable).Value = "NOR Halle 430" Then
Range("V" & ivariable).Value = (Range("E" & ivariable).Value - Range("D" & ivariable).Value) * _
_
_
24
End If
ivariable = ivariable + 1
Loop Until Range("A" & ivariable) = ""
'Filter Makros aufrufen
Call Filtern_eins
Worksheets("Tabelle1").Select
ActiveSheet.Range("$A$1:$V$2438").AutoFilter Field:=15
Call Filtern_zwei
Worksheets("Tabelle1").Select
ActiveSheet.Range("$A$1:$V$2438").AutoFilter Field:=16
Call Filtern_drei
Worksheets("Tabelle1").Select
ActiveSheet.Range("$A$1:$V$2438").AutoFilter Field:=17
Call Filtern_vier
Worksheets("Tabelle1").Select
ActiveSheet.Range("$A$1:$V$2438").AutoFilter Field:=18
Call Filtern_fuenf
Worksheets("Tabelle1").Select
ActiveSheet.Range("$A$1:$V$2438").AutoFilter Field:=19
Call Filtern_sechs
Worksheets("Tabelle1").Select
ActiveSheet.Range("$A$1:$V$2438").AutoFilter Field:=20
Call Filtern_sieben
Worksheets("Tabelle1").Select
ActiveSheet.Range("$A$1:$V$2438").AutoFilter Field:=21
Call Filtern_acht
Worksheets("Tabelle1").Select
ActiveSheet.Range("$A$1:$V$2438").AutoFilter Field:=22
'Erstellung der Diagramme
Call diagramm_eins
Call diagramm_zwei
Call diagramm_drei
Call diagramm_vier
Call diagramm_fuenf
Call diagramm_sechs
Call diagramm_sieben
Call diagramm_acht
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Call Calculate
End Sub
Modul Filtern und Kopieren:
Option Explicit
Sub Filtern_eins()
ActiveSheet.Range("$A$1:$V$2438").AutoFilter Field:=15, Criteria1:=""
Columns("A:O").Select
Selection.Copy
Sheets("Tabelle2").Select
Range("A1").Select
ActiveSheet.Paste
End Sub
Sub Filtern_zwei()
ActiveSheet.Range("$A$1:$V$2438").AutoFilter Field:=16, Criteria1:=""
Range("A:N,P:P").Select
Selection.Copy
Sheets("Tabelle3").Select
Range("A1").Select
ActiveSheet.Paste
End Sub
Sub Filtern_drei()
ActiveSheet.Range("$A$1:$V$2438").AutoFilter Field:=17, Criteria1:=""
Range("A:N,Q:Q").Select
Selection.Copy
Sheets("Tabelle4").Select
Range("A1").Select
ActiveSheet.Paste
End Sub
Sub Filtern_vier()
ActiveSheet.Range("$A$1:$V$2438").AutoFilter Field:=18, Criteria1:=""
Range("A:N,R:R").Select
Selection.Copy
Sheets("Tabelle5").Select
Range("A1").Select
ActiveSheet.Paste
End Sub
Sub Filtern_fuenf()
ActiveSheet.Range("$A$1:$V$2438").AutoFilter Field:=19, Criteria1:=""
Range("A:N,S:S").Select
Selection.Copy
Sheets("Tabelle6").Select
Range("A1").Select
ActiveSheet.Paste
End Sub
Sub Filtern_sechs()
ActiveSheet.Range("$A$1:$V$2438").AutoFilter Field:=20, Criteria1:=""
Range("A:N,T:T").Select
Selection.Copy
Sheets("Tabelle7").Select
Range("A1").Select
ActiveSheet.Paste
End Sub
Sub Filtern_sieben()
ActiveSheet.Range("$A$1:$V$2438").AutoFilter Field:=21, Criteria1:=""
Range("A:N,U:U").Select
Selection.Copy
Sheets("Tabelle8").Select
Range("A1").Select
ActiveSheet.Paste
End Sub
Sub Filtern_acht()
ActiveSheet.Range("$A$1:$V$2438").AutoFilter Field:=22, Criteria1:=""
Range("A:N,V:V").Select
Selection.Copy
Sheets("Tabelle9").Select
Range("A1").Select
ActiveSheet.Paste
End Sub
Modul Pivot Diagram Erstellung:
Option Explicit
Sub diagramm_eins()
' Makro1 Makro
Sheets("Tabelle2").Select
Columns("A:O").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Tabelle2!A:O", Version:=xlPivotTableVersion15). _
CreatePivotTable TableDestination:="Tabelle2!R1C17", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion15
Sheets("Tabelle2").Select
Cells(1, 17).Select
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("Tabelle2!$Q$1:$S$18")
With ActiveChart.PivotLayout.PivotTable.PivotFields("Start_Datum")
.Orientation = xlRowField
.Position = 1
End With
ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
PivotTable.PivotFields("MTTR/UC-10#NOR"), "Anzahl von MTTR/UC-10#NOR", xlCount
Range("Q2").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
False, True, False, False)
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Anzahl von MTTR/UC-10#NOR")
.Caption = "Summe von MTTR/UC-10#NOR"
.Function = xlSum
End With
End Sub
Sub diagramm_zwei()
' Makro1 Makro
Sheets("Tabelle3").Select
Columns("A:O").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Tabelle3!A:O", Version:=xlPivotTableVersion15). _
CreatePivotTable TableDestination:="Tabelle3!R1C17", TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion15
Sheets("Tabelle3").Select
Cells(1, 17).Select
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("Tabelle3!$Q$1:$S$18")
With ActiveChart.PivotLayout.PivotTable.PivotFields("Start_Datum")
.Orientation = xlRowField
.Position = 1
End With
ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
PivotTable.PivotFields("MTTR/UC-1B, 1N, 1T#NOR"), "Anzahl von MTTR/UC-1B, 1N, 1T#NOR", _
xlCount
Range("Q2").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
False, True, False, False)
With ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"Anzahl von MTTR/UC-1B, 1N, 1T#NOR")
.Caption = "Summe von MTTR/UC-1B, 1N, 1T#NOR"
.Function = xlSum
End With
End Sub
Sub diagramm_drei()
' Makro1 Makro
Sheets("Tabelle4").Select
Columns("A:O").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Tabelle4!A:O", Version:=xlPivotTableVersion15). _
CreatePivotTable TableDestination:="Tabelle4!R1C17", TableName:= _
"PivotTable3", DefaultVersion:=xlPivotTableVersion15
Sheets("Tabelle4").Select
Cells(1, 17).Select
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("Tabelle4!$Q$1:$S$18")
With ActiveChart.PivotLayout.PivotTable.PivotFields("Start_Datum")
.Orientation = xlRowField
.Position = 1
End With
ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
PivotTable.PivotFields("MTTR/UC-10#PONT"), "Anzahl von MTTR/UC-10#PONT", xlCount
Range("Q2").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
False, True, False, False)
With ActiveSheet.PivotTables("PivotTable3").PivotFields( _
"Anzahl von MTTR/UC-10#PONT")
.Caption = "Summe von MTTR/UC-10#PONT"
.Function = xlSum
End With
End Sub
Sub diagramm_vier()
' Makro1 Makro
Sheets("Tabelle5").Select
Columns("A:O").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Tabelle5!A:O", Version:=xlPivotTableVersion15). _
CreatePivotTable TableDestination:="Tabelle5!R1C17", TableName:= _
"PivotTable4", DefaultVersion:=xlPivotTableVersion15
Sheets("Tabelle5").Select
Cells(1, 17).Select
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("Tabelle5!$Q$1:$S$18")
With ActiveChart.PivotLayout.PivotTable.PivotFields("Start_Datum")
.Orientation = xlRowField
.Position = 1
End With
ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
PivotTable.PivotFields("MTTR/UC-1B,1N,1T#PONT"), "Anzahl von MTTR/UC-1B,1N,1T#PONT", _
xlCount
Range("Q2").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
False, True, False, False)
With ActiveSheet.PivotTables("PivotTable4").PivotFields( _
"Anzahl von MTTR/UC-1B,1N,1T#PONT")
.Caption = "Summe von MTTR/UC-1B,1N,1T#PONT"
.Function = xlSum
End With
End Sub
Sub diagramm_fuenf()
' Makro1 Makro
Sheets("Tabelle6").Select
Columns("A:O").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Tabelle6!A:O", Version:=xlPivotTableVersion15). _
CreatePivotTable TableDestination:="Tabelle6!R1C17", TableName:= _
"PivotTable5", DefaultVersion:=xlPivotTableVersion15
Sheets("Tabelle6").Select
Cells(1, 17).Select
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("Tabelle6!$Q$1:$S$18")
With ActiveChart.PivotLayout.PivotTable.PivotFields("Start_Datum")
.Orientation = xlRowField
.Position = 1
End With
ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
PivotTable.PivotFields("MTTR/UC-10#PONM"), "Anzahl von MTTR/UC-10#PONM", xlCount
Range("Q2").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
False, True, False, False)
With ActiveSheet.PivotTables("PivotTable5").PivotFields( _
"Anzahl von MTTR/UC-10#PONM")
.Caption = "Summe von MTTR/UC-10#PONM"
.Function = xlSum
End With
End Sub
Sub diagramm_sechs()
' Makro1 Makro
Sheets("Tabelle7").Select
Columns("A:O").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Tabelle7!A:O", Version:=xlPivotTableVersion15). _
CreatePivotTable TableDestination:="Tabelle7!R1C17", TableName:= _
"PivotTable6", DefaultVersion:=xlPivotTableVersion15
Sheets("Tabelle7").Select
Cells(1, 17).Select
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("Tabelle7!$Q$1:$S$18")
With ActiveChart.PivotLayout.PivotTable.PivotFields("Start_Datum")
.Orientation = xlRowField
.Position = 1
End With
ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
PivotTable.PivotFields("MTTR/UC-1B,1N,1T#PONM"), "Anzahl von MTTR/UC-1B,1N,1T#PONM", _
xlCount
Range("Q2").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
False, True, False, False)
With ActiveSheet.PivotTables("PivotTable6").PivotFields( _
"Anzahl von MTTR/UC-1B,1N,1T#PONM")
.Caption = "Summe von MTTR/UC-1B,1N,1T#PONM"
.Function = xlSum
End With
End Sub
Sub diagramm_sieben()
' Makro1 Makro
Sheets("Tabelle8").Select
Columns("A:O").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Tabelle8!A:O", Version:=xlPivotTableVersion15). _
CreatePivotTable TableDestination:="Tabelle8!R1C17", TableName:= _
"PivotTable7", DefaultVersion:=xlPivotTableVersion15
Sheets("Tabelle8").Select
Cells(1, 17).Select
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("Tabelle8!$Q$1:$S$18")
With ActiveChart.PivotLayout.PivotTable.PivotFields("Start_Datum")
.Orientation = xlRowField
.Position = 1
End With
ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
PivotTable.PivotFields("MTTR/UC-10#PONC"), "Anzahl von MTTR/UC-10#PONC", xlCount
Range("Q2").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
False, True, False, False)
With ActiveSheet.PivotTables("PivotTable7").PivotFields( _
"Anzahl von MTTR/UC-10#PONC")
.Caption = "Summe von MTTR/UC-10#PONC"
.Function = xlSum
End With
End Sub
Sub diagramm_acht()
' Makro1 Makro
Sheets("Tabelle9").Select
Columns("A:O").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Tabelle9!A:O", Version:=xlPivotTableVersion15). _
CreatePivotTable TableDestination:="Tabelle9!R1C17", TableName:= _
"PivotTable8", DefaultVersion:=xlPivotTableVersion15
Sheets("Tabelle9").Select
Cells(1, 17).Select
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("Tabelle9!$Q$1:$S$18")
With ActiveChart.PivotLayout.PivotTable.PivotFields("Start_Datum")
.Orientation = xlRowField
.Position = 1
End With
ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
PivotTable.PivotFields("MTTR/UC-1B,1N,1T#PONC"), "Anzahl von MTTR/UC-1B,1N,1T#PONC", _
xlCount
Range("Q2").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
False, True, False, False)
With ActiveSheet.PivotTables("PivotTable8").PivotFields( _
"Anzahl von MTTR/UC-1B,1N,1T#PONC")
.Caption = "Summe von MTTR/UC-1B,1N,1T#PONC"
.Function = xlSum
End With
End Sub