Hallo WOW Danke,
aber ich bin in VBA nicht so gut. Hier ist mein fertiger Code. Wo muss ich am besten euren Code einfügen?
Sub Test()
' Makro10 Makro
Workbooks.OpenText Filename:="C:\Users\Tim\Desktop\Analyse3.txt", Origin _
:=xlWindows, StartRow:=6, DataType:=xlFixedWidth, FieldInfo:=Array(Array _
(0, 1), Array(21, 1), Array(42, 1), Array(83, 2), Array(102, 1), Array(111, 1), Array( _
120, 1 _
), Array(130, 1), Array(139, 1), Array(145, 1), Array(154, 1), Array(170, 1)), _
TrailingMinusNumbers:=True
Application.ScreenUpdating = False
Columns("A:A").Copy
Columns("M:M").PasteSpecial
Range("M2").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1") = "a"
Range("A1").AutoFill Destination:=Range("A1:M1"), Type:=xlFillDefault
Range("A1:M1").AutoFilter
ActiveWorkbook.Worksheets("Analyse3").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Analyse3").AutoFilter.Sort.SortFields.Add Key:= _
Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Analyse3").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Range("$A$1:$M$800000").AutoFilter Field:=9, Criteria1:="=eFD1", _
Operator:=xlOr, Criteria2:="=eTS1"
Columns("A:M").Copy
Sheets.Add After:=Sheets(Sheets.Count)
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("G:G").NumberFormat = "m/d/yyyy"
Columns("H:H").Select
Selection.EntireColumn.Hidden = True
Range("A1") = "WE Vorgang"
Range("B1") = "Artikelnr"
Range("C1") = "Bezeichnung"
Range("D1") = "TE"
Range("E1") = "Menge"
Range("F1") = "Auspr."
Range("G1") = "WE Datum"
Range("I1") = "Bereich"
Columns("J:J").EntireColumn.Hidden = True
Columns("K:K").EntireColumn.Hidden = True
Columns("L:L").EntireColumn.AutoFit
Range("L1") = "Sor."
Range("M1") = "Paletten TYP"
Columns("A:A").Copy
Sheets.Add After:=Sheets(Sheets.Count)
Columns("A:A").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Range("$A$1:$A$1046826").RemoveDuplicates Columns:=1, Header:= _
xlYes
Range("B1") = "Datum"
Range("C1") = "EP*"
Range("D1") = "CHP*"
Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("C1") = "KW"
Range("F1") = "EWP*"
Range("g1") = "LPR*"
Range("D2") = "=COUNTIFS(Tabelle1!C1,Tabelle2!RC1,Tabelle1!C13,R1C4)"
Range("E2") = "=COUNTIFS(Tabelle1!C1,Tabelle2!RC1,Tabelle1!C13,R1C5)"
Range("B2") = "=VLOOKUP(RC[-1],Tabelle1!C[-1]:C[5],7,FALSE)"
Range("C2") = "=WEEKNUM(RC[-1],21)"
Range("F2") = "=COUNTIFS(Tabelle1!C1,Tabelle2!RC1,Tabelle1!C13,R1C6)"
Range("g2") = "=COUNTIFS(Tabelle1!C1,Tabelle2!RC1,Tabelle1!C13,R1C7)"
Columns("C:C").EntireColumn.AutoFit
Range("B2:g2").AutoFill Destination:=Range("B2:g" & Cells(Rows.Count, "A").End(xlUp).Row), _
Type:=xlFillDefault
Range("D1:g1").Copy
Range("I1").PasteSpecial
Range("I2") = "=SUM(C[-5])"
Range("J2") = "=SUM(C[-5])"
Range("K2") = "=SUM(C[-5])"
Range("L1") = "LPR*"
Range("L2") = "=SUM(C[-5])"
With Range("i1:l2") 'Farben
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("i1:l1").Interior.ColorIndex = 8
With Range("i1:l2")
.Font.Bold = True
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlInsideVertical).Weight = xlThin
.Borders(xlInsideHorizontal).Weight = xlThin
End With
Columns("B:B").Copy
Columns("N:N").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Range("$N$1:$N$1030439").RemoveDuplicates Columns:=1, Header:= _
xlNo
ActiveWorkbook.Worksheets("Tabelle2").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Tabelle2").Sort.SortFields.Add Key:=Range( _
"N2:N1030439"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Tabelle2").Sort
.SetRange Range("N1:N1030439")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("N2:N8").Copy
Range("O1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("D1:G1").Select
Application.CutCopyMode = False
Selection.Copy
Range("N2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
With Range("n1:u5")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("n1").Interior.ColorIndex = 17
With Range("n1:lu5")
.Font.Bold = True
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlInsideVertical).Weight = xlThin
.Borders(xlInsideHorizontal).Weight = xlThin
End With
Range("O2") = "=IF(SUMIFS(C4,C2,R[-1]C)=0,"""",SUMIFS(C4,C2,R[-1]C))"
Range("O3") = "=IF(SUMIFS(C5,C2,R[-2]C)=0,"""",SUMIFS(C5,C2,R[-2]C))"
Range("O4") = "=IF(SUMIFS(C6,C2,R[-3]C)=0,"""",SUMIFS(C6,C2,R[-3]C))"
Range("O2:O5").Select
Selection.AutoFill Destination:=Range("O2:U5"), Type:=xlFillDefault
Range("O2:U5").Select
Sheets("Tabelle2").Name = "Auswertung"
Sheets("Tabelle1").Name = "Daten"
Sheets("Analyse3").Name = "Grunddaten"
Range("N6:N9").ClearContents
Columns("C:G").EntireColumn.AutoFit
Range("H1").Select
Application.ScreenUpdating = True
MsgBox "Daten wurden hergestellt!"
End Sub
Danke