|
Hallo Community,
ich habe eine allgemeine Frage: Ich habe ein Makro geschrieben, wo ich verschiedene Pfade definiert habe und damit vier unterschiedliche Arbeitsmappen öffne und bestimmte Daten in meine eigentliche Arbeitsmappe herhole.
Dabei sind 32 Spalten und 17000 Zeilen herausgekommen. Die Daten der 32 Spalten werden über Summewenns-Fkt. erstellt.
Ich habs auch schon mit Application.XLmanual versucht aber das Makro braucht unendlich lang, bis es dann irgendwann "aufgibt".
Unten nochmal das Makro, vllt findet ihr Verbesserungspotenziale oder -vorschläge:
------------------------------------------------------------------------------------------------------
Sub Übersicht()
Application.ScreenUpdating = False
Dim i, ende, a, x, ii, b As Long
Dim IngLast As Long
Dim IngLast2 As Long
Dim IngLast3 As Long
Dim wb As Workbook
Dim spalte As Long
Pfad1 = ActiveWorkbook.Sheets("Pfad").Cells(5, 2).Value
Workbooks.Open Filename:=Pfad1
Set wb = Workbooks.Open(Pfad1)
Sheets("Verf_Ist_Obl").Select
Range("E14").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Worksheets("Tabelle1").Activate
Sheets("Tabelle1").Cells(2, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
wb.Close Savechanges:=False
ThisWorkbook.Worksheets("Tabelle1").Cells(1, 1) = "Projekt-Nr."
IngLast = Cells(Rows.Count, 1).End(xlUp).Row + 1
Application.DisplayAlerts = False
Pfad2 = ActiveWorkbook.Sheets("Pfad").Cells(6, 2).Value
Workbooks.Open Filename:=Pfad2, UpdateLinks:=0
Set wb = Workbooks.Open(Pfad2)
Sheets("Planung").Select
ActiveSheet.Range("$A$7:$BP$4438").AutoFilter Field:=22, Operator:=xlFilterAutomaticFontColor
ActiveSheet.Range("$A$7:$BP$4438").AutoFilter Field:=14, Criteria1:=Array("9231000/FL/Seminar", "9250000/FL/ANÜ", "9276000/FL/Bildung", "9420000/FL/Umbau_Masch.", "9422000/FL/Rep.", "9710000/FL/ANÜ_RK", "9715000/FL/ANÜ_RZ", "9760000/FL/IT_Miete", "9800000/FL/WV", "9801000/FL", "9801000/FL/EZ", "9802000/LEK/Serie", "9803000/LEK/Änd.", "9849000/FL/sonst_FL", "="), Operator:=xlFilterValues
ActiveSheet.Range("$A$7:$BP$4438").AutoFilter Field:=10, Criteria1:="<>", VisibleDropDown:=False
Range("J13").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ThisWorkbook.Activate
Sheets("Tabelle1").Cells(IngLast, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
wb.Close Savechanges:=False
IngLast2 = Cells(Rows.Count, 1).End(xlUp).Row + 1
Pfad3 = ActiveWorkbook.Sheets("Pfad").Cells(7, 2).Value
Workbooks.Open Filename:=Pfad3, UpdateLinks:=0
Set wb = Workbooks.Open(Pfad3)
Range("C4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
Sheets("Tabelle1").Cells(IngLast2, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
wb.Close Savechanges:=False
IngLast3 = Cells(Rows.Count, 1).End(xlUp).Row + 1
Pfad4 = ActiveWorkbook.Sheets("Pfad").Cells(8, 2).Value
Workbooks.Open Filename:=Pfad4
Set wb = Workbooks.Open(Pfad4)
Range("S2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ThisWorkbook.Activate
Sheets("Tabelle1").Cells(IngLast3, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
wb.Close Savechanges:=False
Application.DisplayAlerts = True
ThisWorkbook.Activate
Columns("A:A").Select
ActiveSheet.Range("$A$1:$A$100000").RemoveDuplicates Columns:=1, Header:= _
xlNo
Application.ThisWorkbook.RefreshAll
'----------------------------------------------------------------------------
'Erstellen des neuen Reiters "Übersicht"
Worksheets.Add.Name = "Übersicht"
ende = Worksheets("Tabelle1").Cells(Rows.Count, 1).End(xlUp).Row
'Schleife "Rüberkopieren" der Projekt-Nummern in Reiter "Übersicht" -> pro Projektnr. 32 Zeilen für jede Abteilung (alte u neue Org. EF-1 zu EF-P)
ThisWorkbook.Worksheets("Übersicht").Cells(1, 1) = "Projekt-Nr."
x = 2
For i = 2 To ende
a = x
Worksheets("Übersicht").Cells(a, 1) = Sheets("Tabelle1").Cells(i, 1).Value
Worksheets("Übersicht").Cells(a + 1, 1) = Sheets("Tabelle1").Cells(i, 1).Value
Worksheets("Übersicht").Cells(a + 2, 1) = Sheets("Tabelle1").Cells(i, 1).Value
Worksheets("Übersicht").Cells(a + 3, 1) = Sheets("Tabelle1").Cells(i, 1).Value
Worksheets("Übersicht").Cells(a + 4, 1) = Sheets("Tabelle1").Cells(i, 1).Value
Worksheets("Übersicht").Cells(a + 5, 1) = Sheets("Tabelle1").Cells(i, 1).Value
Worksheets("Übersicht").Cells(a + 6, 1) = Sheets("Tabelle1").Cells(i, 1).Value
Worksheets("Übersicht").Cells(a + 7, 1) = Sheets("Tabelle1").Cells(i, 1).Value
Worksheets("Übersicht").Cells(a + 8, 1) = Sheets("Tabelle1").Cells(i, 1).Value
Worksheets("Übersicht").Cells(a + 9, 1) = Sheets("Tabelle1").Cells(i, 1).Value
Worksheets("Übersicht").Cells(a + 10, 1) = Sheets("Tabelle1").Cells(i, 1).Value
Worksheets("Übersicht").Cells(a + 11, 1) = Sheets("Tabelle1").Cells(i, 1).Value
Worksheets("Übersicht").Cells(a + 12, 1) = Sheets("Tabelle1").Cells(i, 1).Value
Worksheets("Übersicht").Cells(a + 13, 1) = Sheets("Tabelle1").Cells(i, 1).Value
Worksheets("Übersicht").Cells(a + 14, 1) = Sheets("Tabelle1").Cells(i, 1).Value
Worksheets("Übersicht").Cells(a + 15, 1) = Sheets("Tabelle1").Cells(i, 1).Value
Worksheets("Übersicht").Cells(a + 16, 1) = Sheets("Tabelle1").Cells(i, 1).Value
Worksheets("Übersicht").Cells(a + 17, 1) = Sheets("Tabelle1").Cells(i, 1).Value
Worksheets("Übersicht").Cells(a + 18, 1) = Sheets("Tabelle1").Cells(i, 1).Value
Worksheets("Übersicht").Cells(a + 19, 1) = Sheets("Tabelle1").Cells(i, 1).Value
Worksheets("Übersicht").Cells(a + 20, 1) = Sheets("Tabelle1").Cells(i, 1).Value
Worksheets("Übersicht").Cells(a + 21, 1) = Sheets("Tabelle1").Cells(i, 1).Value
Worksheets("Übersicht").Cells(a + 22, 1) = Sheets("Tabelle1").Cells(i, 1).Value
Worksheets("Übersicht").Cells(a + 23, 1) = Sheets("Tabelle1").Cells(i, 1).Value
Worksheets("Übersicht").Cells(a + 24, 1) = Sheets("Tabelle1").Cells(i, 1).Value
Worksheets("Übersicht").Cells(a + 25, 1) = Sheets("Tabelle1").Cells(i, 1).Value
Worksheets("Übersicht").Cells(a + 26, 1) = Sheets("Tabelle1").Cells(i, 1).Value
Worksheets("Übersicht").Cells(a + 27, 1) = Sheets("Tabelle1").Cells(i, 1).Value
Worksheets("Übersicht").Cells(a + 28, 1) = Sheets("Tabelle1").Cells(i, 1).Value
Worksheets("Übersicht").Cells(a + 29, 1) = Sheets("Tabelle1").Cells(i, 1).Value
Worksheets("Übersicht").Cells(a + 30, 1) = Sheets("Tabelle1").Cells(i, 1).Value
Worksheets("Übersicht").Cells(a + 31, 1) = Sheets("Tabelle1").Cells(i, 1).Value
x = x + 32
Next i
y = 2
For ii = 2 To ende
b = y
Worksheets("Übersicht").Cells(b, 2) = ActiveWorkbook.Sheets("Pfad").Cells(9, 1).Value
Worksheets("Übersicht").Cells(b + 1, 2) = ActiveWorkbook.Sheets("Pfad").Cells(10, 1).Value
Worksheets("Übersicht").Cells(b + 2, 2) = ActiveWorkbook.Sheets("Pfad").Cells(11, 1).Value
Worksheets("Übersicht").Cells(b + 3, 2) = ActiveWorkbook.Sheets("Pfad").Cells(12, 1).Value
Worksheets("Übersicht").Cells(b + 4, 2) = ActiveWorkbook.Sheets("Pfad").Cells(13, 1).Value
Worksheets("Übersicht").Cells(b + 5, 2) = ActiveWorkbook.Sheets("Pfad").Cells(14, 1).Value
Worksheets("Übersicht").Cells(b + 6, 2) = ActiveWorkbook.Sheets("Pfad").Cells(15, 1).Value
Worksheets("Übersicht").Cells(b + 7, 2) = ActiveWorkbook.Sheets("Pfad").Cells(16, 1).Value
Worksheets("Übersicht").Cells(b + 8, 2) = ActiveWorkbook.Sheets("Pfad").Cells(17, 1).Value
Worksheets("Übersicht").Cells(b + 9, 2) = ActiveWorkbook.Sheets("Pfad").Cells(18, 1).Value
Worksheets("Übersicht").Cells(b + 10, 2) = ActiveWorkbook.Sheets("Pfad").Cells(19, 1).Value
Worksheets("Übersicht").Cells(b + 11, 2) = ActiveWorkbook.Sheets("Pfad").Cells(20, 1).Value
Worksheets("Übersicht").Cells(b + 12, 2) = ActiveWorkbook.Sheets("Pfad").Cells(21, 1).Value
Worksheets("Übersicht").Cells(b + 13, 2) = ActiveWorkbook.Sheets("Pfad").Cells(22, 1).Value
Worksheets("Übersicht").Cells(b + 14, 2) = ActiveWorkbook.Sheets("Pfad").Cells(23, 1).Value
Worksheets("Übersicht").Cells(b + 15, 2) = ActiveWorkbook.Sheets("Pfad").Cells(24, 1).Value
Worksheets("Übersicht").Cells(b + 16, 2) = ActiveWorkbook.Sheets("Pfad").Cells(25, 1).Value
Worksheets("Übersicht").Cells(b + 17, 2) = ActiveWorkbook.Sheets("Pfad").Cells(26, 1).Value
Worksheets("Übersicht").Cells(b + 18, 2) = ActiveWorkbook.Sheets("Pfad").Cells(27, 1).Value
Worksheets("Übersicht").Cells(b + 19, 2) = ActiveWorkbook.Sheets("Pfad").Cells(28, 1).Value
Worksheets("Übersicht").Cells(b + 20, 2) = ActiveWorkbook.Sheets("Pfad").Cells(29, 1).Value
Worksheets("Übersicht").Cells(b + 21, 2) = ActiveWorkbook.Sheets("Pfad").Cells(30, 1).Value
Worksheets("Übersicht").Cells(b + 22, 2) = ActiveWorkbook.Sheets("Pfad").Cells(31, 1).Value
Worksheets("Übersicht").Cells(b + 23, 2) = ActiveWorkbook.Sheets("Pfad").Cells(32, 1).Value
Worksheets("Übersicht").Cells(b + 24, 2) = ActiveWorkbook.Sheets("Pfad").Cells(33, 1).Value
Worksheets("Übersicht").Cells(b + 25, 2) = ActiveWorkbook.Sheets("Pfad").Cells(34, 1).Value
Worksheets("Übersicht").Cells(b + 26, 2) = ActiveWorkbook.Sheets("Pfad").Cells(35, 1).Value
Worksheets("Übersicht").Cells(b + 27, 2) = ActiveWorkbook.Sheets("Pfad").Cells(36, 1).Value
Worksheets("Übersicht").Cells(b + 28, 2) = ActiveWorkbook.Sheets("Pfad").Cells(37, 1).Value
Worksheets("Übersicht").Cells(b + 29, 2) = ActiveWorkbook.Sheets("Pfad").Cells(38, 1).Value
Worksheets("Übersicht").Cells(b + 30, 2) = ActiveWorkbook.Sheets("Pfad").Cells(39, 1).Value
Worksheets("Übersicht").Cells(b + 31, 2) = ActiveWorkbook.Sheets("Pfad").Cells(40, 1).Value
y = y + 32
Next ii
'Beschriftung der Spalten für die Verfügtwerte
ThisWorkbook.Worksheets("Übersicht").Cells(1, 2) = "Abteilung"
ThisWorkbook.Worksheets("Übersicht").Cells(1, 3) = "EL Verf."
ThisWorkbook.Worksheets("Übersicht").Cells(1, 4) = "FL Verf."
ThisWorkbook.Worksheets("Übersicht").Cells(1, 5) = "EZ Verf."
ThisWorkbook.Worksheets("Übersicht").Cells(1, 6) = "HW Verf."
ThisWorkbook.Worksheets("Übersicht").Cells(1, 7) = "LEK Verf."
ende2 = Worksheets("Übersicht").Cells(Rows.Count, 1).End(xlUp).Row
Application.DisplayAlerts = False
Workbooks.Open Filename:=Pfad1
Set wb = Workbooks.Open(Pfad1)
ThisWorkbook.Activate
ThisWorkbook.Sheets("Übersicht").Select
Application.Calculation = xlManual
'Summewenns-Formel EL Verfügt
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('[aktueller Monatsabschluss.xlsx]Verf_Ist_Obl'!C9,'[aktueller Monatsabschluss.xlsx]Verf_Ist_Obl'!C5,RC[-2],'[aktueller Monatsabschluss.xlsx]Verf_Ist_Obl'!C2,RC[-1])"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C" & ende2)
Range("C2:C" & ende2).Select
'Summewenns-Formel FL Verfügt
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=(SUMIFS('[aktueller Monatsabschluss.xlsx]Verf_Ist_Obl'!C14,'[aktueller Monatsabschluss.xlsx]Verf_Ist_Obl'!C5,RC[-3],'[aktueller Monatsabschluss.xlsx]Verf_Ist_Obl'!C2,RC[-2]))-(SUMIFS('[aktueller Monatsabschluss.xlsx]Verf_Ist_Obl'!C13,'[aktueller Monatsabschluss.xlsx]Verf_Ist_Obl'!C5,RC[-3],'[aktueller Monatsabschluss.xlsx]Verf_Ist_Obl'!C2,RC[-2]))"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D" & ende2)
Range("D2:D" & ende2).Select
'Summewenns-Formel EZ Verfügt
Range("E2").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('[aktueller Monatsabschluss.xlsx]Verf_Ist_Obl'!C13,'[aktueller Monatsabschluss.xlsx]Verf_Ist_Obl'!C5,RC[-4],'[aktueller Monatsabschluss.xlsx]Verf_Ist_Obl'!C2,RC[-3])"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E" & ende2)
Range("E2:E" & ende2).Select
'Summewenns-Formel HW Verfügt
Range("F2").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('[aktueller Monatsabschluss.xlsx]Verf_Ist_Obl'!C20,'[aktueller Monatsabschluss.xlsx]Verf_Ist_Obl'!C5,RC[-5],'[aktueller Monatsabschluss.xlsx]Verf_Ist_Obl'!C2,RC[-4])"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F" & ende2)
Range("F2:F" & ende2).Select
'Summewenns-Formel LEK Verfügt
Range("G2").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('[aktueller Monatsabschluss.xlsx]Verf_Ist_Obl'!C24,'[aktueller Monatsabschluss.xlsx]Verf_Ist_Obl'!C5,RC[-6],'[aktueller Monatsabschluss.xlsx]Verf_Ist_Obl'!C2,RC[-5])"
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G" & ende2)
Range("G2:G" & ende2).Select
Calculate
Application.Calculation = xlAutomatic
Application.Calculation = xlCalculationManual
Columns("A:G").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.Calculation = xlCalculationAutomatic
'Beschriftung der Spalten für die Hochrechnung
ThisWorkbook.Worksheets("Übersicht").Cells(1, 8) = "EL HR"
ThisWorkbook.Worksheets("Übersicht").Cells(1, 9) = "FL Pipeline"
ThisWorkbook.Worksheets("Übersicht").Cells(1, 10) = "EZ Pipeline"
ThisWorkbook.Worksheets("Übersicht").Cells(1, 11) = "HW FC"
ThisWorkbook.Worksheets("Übersicht").Cells(1, 12) = "LEK Pipeline"
'Werte hart reinkopieren
Application.CutCopyMode = False
wb.Close Savechanges:=False
'Ende Verfügtwerte
'HW-Werte aus FC holen
Workbooks.Open Filename:=Pfad3, UpdateLinks:=0
Set wb = Workbooks.Open(Pfad3)
ThisWorkbook.Activate
Range("K2").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('[HW (HR).xlsx]HW'!C5,'[HW (HR).xlsx]HW'!C3,RC[-10],'[HW (HR).xlsx]HW'!C2,RC[-9])"
Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K" & ende2)
Range("K2:K" & ende2).Select
Columns("K:K").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
wb.Close Savechanges:=False
Application.DisplayAlerts = True
ThisWorkbook.Worksheets("Übersicht").Cells(1, 13) = "GS Kapa Ziel"
ThisWorkbook.Worksheets("Übersicht").Cells(1, 14) = "GS EZ Ziel"
ThisWorkbook.Worksheets("Übersicht").Cells(1, 15) = "GS HW Ziel"
ThisWorkbook.Worksheets("Übersicht").Cells(1, 16) = "GS LEK Ziel"
'Werte aus Auftragsbuch (GS)
spalte = ThisWorkbook.Worksheets("Pfad").Cells(23, 4).Value
Workbooks.Open Filename:=Pfad4
Set wb = Workbooks.Open(Pfad4)
ThisWorkbook.Activate
ThisWorkbook.Sheets("Übersicht").Cells(2, 13).Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte & "],'[kons_AB.xlsx]LUP Planung'!C20,""Kapa"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-11],'[kons_AB.xlsx]LUP Planung'!C19,RC[-12],'[kons_AB.xlsx]LUP Planung'!C5,""GS"")"
Range("M2").Select
Selection.AutoFill Destination:=Range("M2:M" & ende2)
Range("N2").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 1 & "],'[kons_AB.xlsx]LUP Planung'!C20,""EZ"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-12],'[kons_AB.xlsx]LUP Planung'!C19,RC[-13],'[kons_AB.xlsx]LUP Planung'!C5,""GS"")"
Range("N2").Select
Selection.AutoFill Destination:=Range("N2:N" & ende2)
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=(SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 2 & "],'[kons_AB.xlsx]LUP Planung'!C20,""HW"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-13],'[kons_AB.xlsx]LUP Planung'!C19,RC[-14],'[kons_AB.xlsx]LUP Planung'!C5,""GS""))+(SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 2 & "],'[kons_AB.xlsx]LUP Planung'!C20,""sonst. HW"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-13],'[kons_AB.xlsx]LUP Planung'!C19,RC[-14],'[kons_AB.xlsx]LUP Planung'!C5,""GS""))+(SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 2 & "],'[kons_AB.xlsx]LUP Planung'!C20,""NSG"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-13],'[kons_AB.xlsx]LUP Planung'!C19,RC[-14],'[kons_AB.xlsx]LUP Planung'!C5,""GS""))+(SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 2 & "],'[kons_AB.xlsx]LUP Planung'!C20,""VGB"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-13],'[kons_AB.xlsx]LUP Planung'!C19,RC[-14],'[kons_AB.xlsx]LUP Planung'!C5,""GS""))"
Range("O2").Select
Selection.AutoFill Destination:=Range("O2:O" & ende2)
Range("P2").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 3 & "],'[kons_AB.xlsx]LUP Planung'!C20,""LEK"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-14],'[kons_AB.xlsx]LUP Planung'!C19,RC[-15],'[kons_AB.xlsx]LUP Planung'!C5,""GS"")"
Range("P2").Select
Selection.AutoFill Destination:=Range("P2:P" & ende2)
'D Werte
ThisWorkbook.Worksheets("Übersicht").Cells(1, 17) = "D Kapa Ziel"
ThisWorkbook.Worksheets("Übersicht").Cells(1, 18) = "D EZ Ziel"
ThisWorkbook.Worksheets("Übersicht").Cells(1, 19) = "D HW Ziel"
ThisWorkbook.Worksheets("Übersicht").Cells(1, 20) = "D LEK Ziel"
Range("Q2").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 4 & "],'[kons_AB.xlsx]LUP Planung'!C20,""Kapa"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-15],'[kons_AB.xlsx]LUP Planung'!C19,RC[-16],'[kons_AB.xlsx]LUP Planung'!C5,""D"")"
Range("Q2").Select
Selection.AutoFill Destination:=Range("Q2:Q" & ende2)
Range("R2").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 5 & "],'[kons_AB.xlsx]LUP Planung'!C20,""EZ"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-16],'[kons_AB.xlsx]LUP Planung'!C19,RC[-17],'[kons_AB.xlsx]LUP Planung'!C5,""D"")"
Range("R2").Select
Selection.AutoFill Destination:=Range("R2:R" & ende2)
Range("S2").Select
ActiveCell.FormulaR1C1 = _
"=(SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 6 & "],'[kons_AB.xlsx]LUP Planung'!C20,""HW"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-17],'[kons_AB.xlsx]LUP Planung'!C19,RC[-18],'[kons_AB.xlsx]LUP Planung'!C5,""D""))+(SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 6 & "],'[kons_AB.xlsx]LUP Planung'!C20,""sonst. HW"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-17],'[kons_AB.xlsx]LUP Planung'!C19,RC[-18],'[kons_AB.xlsx]LUP Planung'!C5,""D""))+(SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 6 & "],'[kons_AB.xlsx]LUP Planung'!C20,""NSG"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-17],'[kons_AB.xlsx]LUP Planung'!C19,RC[-18],'[kons_AB.xlsx]LUP Planung'!C5,""D""))+(SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 6 & "],'[kons_AB.xlsx]LUP Planung'!C20,""VGB"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-17],'[kons_AB.xlsx]LUP Planung'!C19,RC[-18],'[kons_AB.xlsx]LUP Planung'!C5,""D""))"
Range("S2").Select
Selection.AutoFill Destination:=Range("S2:S" & ende2)
Range("T2").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 7 & "],'[kons_AB.xlsx]LUP Planung'!C20,""LEK"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-18],'[kons_AB.xlsx]LUP Planung'!C19,RC[-19],'[kons_AB.xlsx]LUP Planung'!C5,""D"")"
Range("T2").Select
Selection.AutoFill Destination:=Range("T2:T" & ende2)
Application.Calculation = xlCalculationManual
Columns("A:T").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.Calculation = xlCalculationAutomatic
'tsV Werte
ThisWorkbook.Worksheets("Übersicht").Cells(1, 21) = "tsV Kapa Ziel"
ThisWorkbook.Worksheets("Übersicht").Cells(1, 22) = "tsV EZ Ziel"
ThisWorkbook.Worksheets("Übersicht").Cells(1, 23) = "tsV HW Ziel"
ThisWorkbook.Worksheets("Übersicht").Cells(1, 24) = "tsV LEK Ziel"
Range("U2").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 8 & "],'[kons_AB.xlsx]LUP Planung'!C20,""Kapa"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-19],'[kons_AB.xlsx]LUP Planung'!C19,RC[-20],'[kons_AB.xlsx]LUP Planung'!C5,""tsV"")"
Range("U2").Select
Selection.AutoFill Destination:=Range("U2:U" & ende2)
Range("V2").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 9 & "],'[kons_AB.xlsx]LUP Planung'!C20,""EZ"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-20],'[kons_AB.xlsx]LUP Planung'!C19,RC[-21],'[kons_AB.xlsx]LUP Planung'!C5,""tsV"")"
Range("V2").Select
Selection.AutoFill Destination:=Range("V2:V" & ende2)
Range("W2").Select
ActiveCell.FormulaR1C1 = _
"=(SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 10 & "],'[kons_AB.xlsx]LUP Planung'!C20,""HW"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-21],'[kons_AB.xlsx]LUP Planung'!C19,RC[-22],'[kons_AB.xlsx]LUP Planung'!C5,""tsV""))+(SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 10 & "],'[kons_AB.xlsx]LUP Planung'!C20,""sonst. HW"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-21],'[kons_AB.xlsx]LUP Planung'!C19,RC[-22],'[kons_AB.xlsx]LUP Planung'!C5,""tsV""))+(SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 10 & "],'[kons_AB.xlsx]LUP Planung'!C20,""NSG"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-21],'[kons_AB.xlsx]LUP Planung'!C19,RC[-22],'[kons_AB.xlsx]LUP Planung'!C5,""tsV""))+(SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 10 & "],'[kons_AB.xlsx]LUP Planung'!C20,""VGB"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-21],'[kons_AB.xlsx]LUP Planung'!C19,RC[-22],'[kons_AB.xlsx]LUP Planung'!C5,""tsV""))"
Range("W2").Select
Selection.AutoFill Destination:=Range("W2:W" & ende2)
Range("X2").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 11 & "],'[kons_AB.xlsx]LUP Planung'!C20,""LEK"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-22],'[kons_AB.xlsx]LUP Planung'!C19,RC[-23],'[kons_AB.xlsx]LUP Planung'!C5,""tsV"")"
Range("X2").Select
Selection.AutoFill Destination:=Range("X2:X" & ende2)
'ZEB Werte
ThisWorkbook.Worksheets("Übersicht").Cells(1, 25) = "ZEB Kapa Ziel"
ThisWorkbook.Worksheets("Übersicht").Cells(1, 26) = "ZEB EZ Ziel"
ThisWorkbook.Worksheets("Übersicht").Cells(1, 27) = "ZEB HW Ziel"
ThisWorkbook.Worksheets("Übersicht").Cells(1, 28) = "ZEB LEK Ziel"
Range("Y2").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 12 & "],'[kons_AB.xlsx]LUP Planung'!C20,""Kapa"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-23],'[kons_AB.xlsx]LUP Planung'!C19,RC[-24],'[kons_AB.xlsx]LUP Planung'!C5,""ZEB"")"
Range("Y2").Select
Selection.AutoFill Destination:=Range("Y2:Y" & ende2)
Range("Z2").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 13 & "],'[kons_AB.xlsx]LUP Planung'!C20,""EZ"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-24],'[kons_AB.xlsx]LUP Planung'!C19,RC[-25],'[kons_AB.xlsx]LUP Planung'!C5,""ZEB"")"
Range("Z2").Select
Selection.AutoFill Destination:=Range("Z2:Z" & ende2)
Range("AA2").Select
ActiveCell.FormulaR1C1 = _
"=(SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 14 & "],'[kons_AB.xlsx]LUP Planung'!C20,""HW"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-25],'[kons_AB.xlsx]LUP Planung'!C19,RC[-26],'[kons_AB.xlsx]LUP Planung'!C5,""ZEB""))+(SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 14 & "],'[kons_AB.xlsx]LUP Planung'!C20,""sonst. HW"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-25],'[kons_AB.xlsx]LUP Planung'!C19,RC[-26],'[kons_AB.xlsx]LUP Planung'!C5,""ZEB""))+(SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 14 & "],'[kons_AB.xlsx]LUP Planung'!C20,""NSG"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-25],'[kons_AB.xlsx]LUP Planung'!C19,RC[-26],'[kons_AB.xlsx]LUP Planung'!C5,""ZEB""))+(SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 14 & "],'[kons_AB.xlsx]LUP Planung'!C20,""VGB"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-25],'[kons_AB.xlsx]LUP Planung'!C19,RC[-26],'[kons_AB.xlsx]LUP Planung'!C5,""ZEB""))"
Range("AA2").Select
Selection.AutoFill Destination:=Range("AA2:AA" & ende2)
Range("AB2").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 15 & "],'[kons_AB.xlsx]LUP Planung'!C20,""LEK"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-26],'[kons_AB.xlsx]LUP Planung'!C19,RC[-27],'[kons_AB.xlsx]LUP Planung'!C5,""ZEB"")"
Range("AB2").Select
Selection.AutoFill Destination:=Range("AB2:AB" & ende2)
Application.Calculation = xlCalculationManual
Columns("A:AB").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.Calculation = xlCalculationAutomatic
'Z.o. Werte
ThisWorkbook.Worksheets("Übersicht").Cells(1, 29) = "Z.o. Kapa Ziel"
ThisWorkbook.Worksheets("Übersicht").Cells(1, 30) = "Z.o. EZ Ziel"
ThisWorkbook.Worksheets("Übersicht").Cells(1, 31) = "Z.o. HW Ziel"
ThisWorkbook.Worksheets("Übersicht").Cells(1, 32) = "Z.o. LEK Ziel"
Range("AC2").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 16 & "],'[kons_AB.xlsx]LUP Planung'!C20,""Kapa"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-27],'[kons_AB.xlsx]LUP Planung'!C19,RC[-28],'[kons_AB.xlsx]LUP Planung'!C5,""Z.o."")"
Range("AC2").Select
Selection.AutoFill Destination:=Range("AC2:AC" & ende2)
Range("AD2").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 17 & "],'[kons_AB.xlsx]LUP Planung'!C20,""EZ"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-28],'[kons_AB.xlsx]LUP Planung'!C19,RC[-29],'[kons_AB.xlsx]LUP Planung'!C5,""Z.o."")"
Range("AD2").Select
Selection.AutoFill Destination:=Range("AD2:AD" & ende2)
Range("AE2").Select
ActiveCell.FormulaR1C1 = _
"=(SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 18 & "],'[kons_AB.xlsx]LUP Planung'!C20,""HW"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-29],'[kons_AB.xlsx]LUP Planung'!C19,RC[-30],'[kons_AB.xlsx]LUP Planung'!C5,""Z.o.""))+(SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 18 & "],'[kons_AB.xlsx]LUP Planung'!C20,""sonst. HW"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-29],'[kons_AB.xlsx]LUP Planung'!C19,RC[-30],'[kons_AB.xlsx]LUP Planung'!C5,""Z.o.""))+(SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 18 & "],'[kons_AB.xlsx]LUP Planung'!C20,""NSG"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-29],'[kons_AB.xlsx]LUP Planung'!C19,RC[-30],'[kons_AB.xlsx]LUP Planung'!C5,""Z.o.""))+(SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 18 & "],'[kons_AB.xlsx]LUP Planung'!C20,""VGB"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-29],'[kons_AB.xlsx]LUP Planung'!C19,RC[-30],'[kons_AB.xlsx]LUP Planung'!C5,""Z.o.""))"
Range("AE2").Select
Selection.AutoFill Destination:=Range("AE2:AE" & ende2)
Range("AF2").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('[kons_AB.xlsx]LUP Planung'!C[" & spalte - 19 & "],'[kons_AB.xlsx]LUP Planung'!C20,""LEK"",'[kons_AB.xlsx]LUP Planung'!C22,RC[-30],'[kons_AB.xlsx]LUP Planung'!C19,RC[-31],'[kons_AB.xlsx]LUP Planung'!C5,""Z.o."")"
Range("AF2").Select
Selection.AutoFill Destination:=Range("AF2:AF" & ende2)
Application.Calculation = xlCalculationManual
Columns("A:AF").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.Calculation = xlCalculationAutomatic
Application.CutCopyMode = False
wb.Close Savechanges:=False
Application.ScreenUpdating = True
End Sub
|