Ich bin neu hier, habe aber schon viel durch try and error aus den Foren gelernt. Ich habe Daten von einer Sortiermaschine (für fast jeden Tag eine) als csv die ich in eine Exceldatei einlesen möchte (jeden Tag in eigenes Blatt). Dabei verändert sich die Position der Daten und auch die Anzahl der zu kopierenden Daten. Ich habe schon einiges hinbekommen, wenn ich aber jetzt für alle Sortierungen die Abfrage laufen lasse und der Suchtext nicht vorkommt hängt das MAkro. Ich denke das es sicherlich deutlich eleganter geht, aber ich bin mit meiner Lösung bisher schon zufrieden. Ich bräuchte jetzt nur Hilfe wie ich nicht vorhandene Suchbegriffe überspringe.
Vielen Dank für die Hilfe
Workbooks.Open Filename:="D:\Daten\Lang_0404.csv", Local:=True
Range("A1").Select
Dim rngStart As Range
Dim rngEnde As Range
Dim rngStart2 As Range
Dim rngEnde2 As Range
Dim Sort1 As Range
Dim Sort2 As Range
Dim Sort3 As Range
Dim Sort4 As Range
Dim Sort5 As Range
Dim Sort6 As Range
Dim Sort7 As Range
Dim Sort8 As Range
Dim Sort9 As Range
Dim Sort10 As Range
Dim Sort11 As Range
Dim Sort12 As Range
Dim Sort13 As Range
Dim Sort14 As Range
Dim Sort15 As Range
Dim Sort16 As Range
Dim Sort17 As Range
Dim Sort18 As Range
Dim Sort19 As Range
Dim Sort20 As Range
Dim Sort21 As Range
Dim ws As Worksheet, wsZiel As Worksheet
Dim rngZelle As Range
Dim rngBereich As Range
Set ws = Worksheets("lang_0404")
Set rngStart = Columns(1).Find(What:="Anzeige der einzelnen Sortierungen als kg")
Set rngEnde = Columns(1).Find(What:="Anzeige der einzelnen Sortierungen als Anteile in Prozent des Gewichtes")
Set TransStart = Columns(1).Find(What:="Gesamtstecherlohn:")
Set transende = Columns(1).Find(What:="Nr,")
Set Sort1 = Columns(2).Find(What:="Tabelle_01 I weiss 30-36")
Set Sort2 = Columns(2).Find(What:="Tabelle_01 I weiss 25-30")
Set Sort3 = Columns(2).Find(What:="Tabelle_01 I weiss 21-25")
Set Sort4 = Columns(2).Find(What:="Tabelle_01 I weiss 16-21")
Set Sort5 = Columns(2).Find(What:="Tabelle_01 I weiss 14+")
Set Sort6 = Columns(2).Find(What:="Tabelle_01 II weiss 16-25")
Set Sort7 = Columns(2).Find(What:="Tabelle_01 II weiss 14+")
Set Sort8 = Columns(2).Find(What:="Tabelle_01 I Vio 16-26")
Set Sort9 = Columns(2).Find(What:="Tabelle_01 II Ws/Vio 30-36")
Set Sort10 = Columns(2).Find(What:="Tabelle_01 II Ws/Vio 25-30")
Set Sort11 = Columns(2).Find(What:="Tabelle_01 II Ws/Vio 21-25")
Set Sort10 = Columns(2).Find(What:="Tabelle_01 II Ws/Vio 25-30")
Set Sort11 = Columns(2).Find(What:="Tabelle_01 II Ws/Vio 21-25")
Set Sort12 = Columns(2).Find(What:="Tabelle_01 II Ws/Vio 16-21")
Set Sort13 = Columns(2).Find(What:="Tabelle_01 II Ws/Vio 14+")
Set Sort14 = Columns(2).Find(What:="Tabelle_01 I Köpfe")
Set Sort15 = Columns(2).Find(What:="Tabelle_01 II Köpfe")
Set Sort16 = Columns(2).Find(What:="Tabelle_01 Rest")
Set Sort17 = Columns(2).Find(What:="Tabelle_10 I 12 16")
Set Sort18 = Columns(2).Find(What:="Tabelle_10 I 8 12")
Set Sort19 = Columns(2).Find(What:="Tabelle_10 II 12+")
Set Sort20 = Columns(2).Find(What:="Tabelle_10 II 8+")
Set Sort21 = Columns(2).Find(What:="nicht zugeordnet")
Range(rngStart.Offset(2, 0), rngEnde.Offset(-3, 0)).Copy
'finde Zeile mit dem Text zu rngStart und beginne 2 Zeilen Später zu markieren bis Text rngEnde 3 zeilen vorher
ActiveWindow.ActivateNext
Range("A5").Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
Range(rngStart.Offset(2, 1), rngEnde.Offset(-3, 2)).Copy
ActiveWindow.ActivateNext
Range("E5").Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
Range("D4").Copy
ActiveWindow.ActivateNext
Range("d5").Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
Range("d16").Select
Dim Zeile As Double
Const Spalte As Integer = 1
Zeile = 16
With ActiveSheet
Do While .Cells(Zeile, Spalte) ""
ActiveCell.Formula = "=TRIM(RC[-2])"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Cut
ActiveCell.Offset(0, -2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 2).Range("A1").Select
Zeile = Zeile + 1
Loop
End With
Range(TransStart.Offset(3, 0), transende.Offset(-2, 1)).Copy
'Transponiere Bereich ab Transstart plus 3 Zeilen bis Transende minus 2 Zeilen plus 1 Spalte
ActiveCell.Offset(1, 2).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
ActiveCell.Offset(1, -1).Range("A1").Select
Cells.Find(What:="Tabelle_01 I weiss 30-36", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(3, 0).Select
With ActiveCell
Range(Cells(.Row, .Column), Cells(325, .Column)).Copy
End With
ActiveWindow.ActivateNext
Range("i5").Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
Range("A1").Select
Cells.Find(What:="Nr,", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(1, 5).Select
Cells.Find(What:="Tabelle_01 I weiss 25-30", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(3, 0).Select
With ActiveCell
Range(Cells(.Row, .Column), Cells(325, .Column)).Copy
End With
ActiveWindow.ActivateNext
Range("j5").Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
Range("A1").Select
Cells.Find(What:="Nr,", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(1, 5).Select
Cells.Find(What:="Tabelle_01 I weiss 21-25", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(3, 0).Select
With ActiveCell
Range(Cells(.Row, .Column), Cells(325, .Column)).Copy
End With
ActiveWindow.ActivateNext
Range("k5").Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
Range("A1").Select
Cells.Find(What:="Nr,", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(1, 5).Select
Cells.Find(What:="Tabelle_01 I weiss 16-21", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(3, 0).Select
With ActiveCell
Range(Cells(.Row, .Column), Cells(325, .Column)).Copy
End With
ActiveWindow.ActivateNext
Range("l5").Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
Range("A1").Select
Cells.Find(What:="Nr,", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(1, 5).Select
Cells.Find(What:="Tabelle_01 I weiss 14+", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(3, 0).Select
With ActiveCell
Range(Cells(.Row, .Column), Cells(325, .Column)).Copy
End With
ActiveWindow.ActivateNext
Range("m5").Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
Range("A1").Select
Cells.Find(What:="Nr,", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(1, 5).Select
Cells.Find(What:="Tabelle_01 II weiss 16-25", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(3, 0).Select
With ActiveCell
Range(Cells(.Row, .Column), Cells(325, .Column)).Copy
End With
ActiveWindow.ActivateNext
Range("n5").Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
Range("A1").Select
Cells.Find(What:="Nr,", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(1, 5).Select
Cells.Find(What:="Tabelle_01 II weiss 14+", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(3, 0).Select
With ActiveCell
Range(Cells(.Row, .Column), Cells(325, .Column)).Copy
End With
ActiveWindow.ActivateNext
Range("o5").Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
Range("A1").Select
Cells.Find(What:="Nr,", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(1, 5).Select
Cells.Find(What:="Tabelle_01 I vio 16-26", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(3, 0).Select
With ActiveCell
Range(Cells(.Row, .Column), Cells(325, .Column)).Copy
End With
ActiveWindow.ActivateNext
Range("p5").Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
Range("A1").Select
Cells.Find(What:="Nr,", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(1, 5).Select
Cells.Find(What:="Tabelle_01 II ws/Vio 30-36", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(3, 0).Select
With ActiveCell
Range(Cells(.Row, .Column), Cells(325, .Column)).Copy
End With
ActiveWindow.ActivateNext
Range("q5").Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
Range("A1").Select
Cells.Find(What:="Nr,", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(1, 5).Select
Cells.Find(What:="Tabelle_01 II ws/Vio 25-30", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(3, 0).Select
With ActiveCell
Range(Cells(.Row, .Column), Cells(325, .Column)).Copy
End With
ActiveWindow.ActivateNext
Range("r5").Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
Range("A1").Select
Cells.Find(What:="Nr,", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(1, 5).Select
Cells.Find(What:="Tabelle_01 II ws/Vio 21-25", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(3, 0).Select
With ActiveCell
Range(Cells(.Row, .Column), Cells(325, .Column)).Copy
End With
ActiveWindow.ActivateNext
Range("s5").Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
Range("A1").Select
Cells.Find(What:="Nr,", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(1, 5).Select
Cells.Find(What:="Tabelle_01 II ws/Vio 16-21", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(3, 0).Select
With ActiveCell
Range(Cells(.Row, .Column), Cells(325, .Column)).Copy
End With
ActiveWindow.ActivateNext
Range("t5").Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
Range("A1").Select
Cells.Find(What:="Nr,", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(1, 5).Select
Cells.Find(What:="Tabelle_01 II ws/Vio 14+", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(3, 0).Select
With ActiveCell
Range(Cells(.Row, .Column), Cells(325, .Column)).Copy
End With
ActiveWindow.ActivateNext
Range("u5").Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
Range("A1").Select
Cells.Find(What:="Nr,", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(1, 5).Select
Cells.Find(What:="Tabelle_01 I K?pfe", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(3, 0).Select
With ActiveCell
Range(Cells(.Row, .Column), Cells(325, .Column)).Copy
End With
ActiveWindow.ActivateNext
Range("v5").Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
Range("A1").Select
Cells.Find(What:="Nr,", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(1, 5).Select
Cells.Find(What:="Tabelle_01 II K?pfe", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(3, 0).Select
With ActiveCell
Range(Cells(.Row, .Column), Cells(325, .Column)).Copy
End With
ActiveWindow.ActivateNext
Range("w5").Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
Range("A1").Select
Cells.Find(What:="Nr,", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(1, 5).Select
Cells.Find(What:="Tabelle_01 Rest", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(3, 0).Select
With ActiveCell
Range(Cells(.Row, .Column), Cells(325, .Column)).Copy
End With
ActiveWindow.ActivateNext
Range("x5").Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
Range("A1").Select
Cells.Find(What:="Nr,", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(1, 5).Select
Cells.Find(What:="Tabelle_10 I 12 16", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(3, 0).Select
With ActiveCell
Range(Cells(.Row, .Column), Cells(325, .Column)).Copy
End With
ActiveWindow.ActivateNext
Range("y5").Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
Range("A1").Select
Cells.Find(What:="Nr,", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(1, 5).Select
Cells.Find(What:="Tabelle_10 I 8 12", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(3, 0).Select
With ActiveCell
Range(Cells(.Row, .Column), Cells(325, .Column)).Copy
End With
ActiveWindow.ActivateNext
Range("z5").Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
Range("A1").Select
Cells.Find(What:="Nr,", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(1, 5).Select
Cells.Find(What:="Tabelle_10 II 12+", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(3, 0).Select
With ActiveCell
Range(Cells(.Row, .Column), Cells(325, .Column)).Copy
End With
ActiveWindow.ActivateNext
Range("aa5").Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
Range("A1").Select
Cells.Find(What:="Nr,", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(1, 5).Select
Cells.Find(What:="Tabelle_10 II 8+", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(3, 0).Select
With ActiveCell
Range(Cells(.Row, .Column), Cells(325, .Column)).Copy
End With
ActiveWindow.ActivateNext
Range("ab5").Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
Range("A1").Select
Cells.Find(What:="Nr,", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(1, 5).Select
Cells.Find(What:="nicht zugeordnet", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(3, 0).Select
With ActiveCell
Range(Cells(.Row, .Column), Cells(325, .Column)).Copy
End With
ActiveWindow.ActivateNext
Range("ac5").Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
Range("A1").Select
Cells.Find(What:="Nr,", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
Activate
ActiveCell.Offset(1, 5).Select
End Sub