hier das Makro, aber ich bin nicht der super Anwender, ich denke viel ist Impro.
Code:
Sub START()
Application.StatusBar = "Daten werden importiert und ausgewertet"
Application.ScreenUpdating = False
Call DATEN_IMPORT
Call DATEN_ANPASSEN
Call DATEN_KOPIEREN_1SD4_1GO5
Call DATEN_KOPIEREN_1GOH
Call DATEN_1SD4_1GO5_SORT
Call DATEN_1GOH_SORT
Application.StatusBar = True
Application.ScreenUpdating = True
End Sub
Sub DATEN_IMPORT()
'
' DATEN_IMPORT Makro
'
'
Windows("Export.xls").Activate
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Windows("SAB_GA_IMPORT.xlsb").Activate
Sheets("Daten_EQX_IMPORT").Select
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C4").Select
End Sub
Sub DATEN_ANPASSEN()
'
' DATEN_ANPASSEN Makro
'
Columns("B:B").Select
Range("B4").Activate
Selection.FormatConditions.Add Type:=xlTextString, String:="EQUINOX", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Rows("22:22").Select
Selection.AutoFilter
Selection.AutoFilter
Range("A20").Select
Selection.Copy
Range("A23:A2000").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Range("$A$22:$W$2000").AutoFilter Field:=1
'
End Sub
Sub DATEN_KOPIEREN_1SD4_1GO5()
'
' DATEN_KOPIEREN Makro
'
'
Sheets("Daten_EQX_IMPORT").Select
Range("E1").Select
Selection.Copy
Range("E22").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Range("$A$22:$HZ$2000").AutoFilter Field:=1, Criteria1:= _
"=*1GO5*", Operator:=xlOr, Criteria2:="=*1SD4*"
Range("B4").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Sheets("Daten_EQX_OUTPUT_1SD4_1GO5").Select
Range("A3").Select
ActiveWindow.SmallScroll Down:=-18
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("H20:AZ20").Select
Selection.Style = "Percent"
End Sub
Sub DATEN_KOPIEREN_1GOH()
'
' DATEN_KOPIEREN Makro
'
'
Sheets("Daten_EQX_IMPORT").Select
Range("E1").Select
Selection.Copy
Range("E22").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Range("$A$22:$HZ$2000").AutoFilter Field:=1, Criteria1:= _
"=*1GOH*", Operator:=xlOr, Criteria2:="=*1GOH*"
Range("B4").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Sheets("Daten_EQX_OUTPUT_1GOH").Select
Range("A3").Select
ActiveWindow.SmallScroll Down:=-18
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("H20:AZ20").Select
Selection.Style = "Percent"
Sheets("Home").Select
End Sub
Sub DATEN_1SD4_1GO5_SORT()
'
' DATEN_1SD4_1GO5_SORT Makro
'
'
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=-33
ActiveWorkbook.Worksheets("Daten_EQX_OUTPUT_1SD4_1GO5").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Daten_EQX_OUTPUT_1SD4_1GO5").Sort.SortFields.Add _
Key:=Range("A2:HZ2"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Daten_EQX_OUTPUT_1SD4_1GO5").Sort
.SetRange Range("A2:HZ2000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Range("P1").Select
'ActiveCell.FormulaR1C1 = "1"
Sheets("Home").Select
Range("A1").Select
End Sub
Sub DATEN_1GOH_SORT()
'
' DATEN_1SD4_1GO5_SORT Makro
'
'
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=-33
ActiveWorkbook.Worksheets("Daten_EQX_OUTPUT_1GOH").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Daten_EQX_OUTPUT_1GOH").Sort.SortFields.Add _
Key:=Range("A2:HZ2"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Daten_EQX_OUTPUT_1GOH").Sort
.SetRange Range("A2:HZ2000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Range("P1").Select
'ActiveCell.FormulaR1C1 = "1"
Sheets("Home").Select
Range("A1").Select
End Sub