HILFE: Zeilen kopieren
26.10.2004 09:35:27
Eleni
Habe hier ein Makro (Code s.u.), welches ich aus vorherigen Forumsanfragen zusammengebacken habe. Es soll Datumseinträge in der Spalte B der Tabelle "Übersicht" durchsuchen und nach Monat sortiert die dazugehörigen Zeilen mitsamt Farmatierung ins entsprechende Tabellenblatt ("September", "Oktober", etc.) kopieren. Im Moment kopiert es nur die Zelle der Spalte B. Mit Row statt Value bekomme ich nur die Zeilen-Nr. ausgegeben. Hilfe! Wie muss es richtig lauten?
Danke schon mal für die hilfreichen Antworten. Eleni
Option Explicit
Sub Trennen()
Dim i As Integer
ThisWorkbook.Sheets("September").Activate
If ThisWorkbook.Sheets("September").Cells(3, 2).Value <> "" Then
ThisWorkbook.Sheets("September").Range("A3", Cells(Rows.Count, 2).End_(xlUp).Offset(0, 14)).Clear
End If
ThisWorkbook.Sheets("Oktober").Activate
If ThisWorkbook.Sheets("Oktober").Cells(3, 2).Value <> "" Then
ThisWorkbook.Sheets("Oktober").Range("A3", Cells(Rows.Count, 2).End(xlUp).Offset(0, 14)).Clear
End If
ThisWorkbook.Sheets("November").Activate
If ThisWorkbook.Sheets("November").Cells(3, 2).Value <> "" Then
ThisWorkbook.Sheets("November").Range("A3", Cells(Rows.Count, 2).End_(xlUp).Offset(0, 14)).Clear
End If
ThisWorkbook.Sheets("Dezember").Activate
If ThisWorkbook.Sheets("Dezember").Cells(3, 2).Value <> "" Then
ThisWorkbook.Sheets("Dezember").Range("A3", Cells(Rows.Count, 2).End_(xlUp).Offset(0, 14)).Clear
End If
ThisWorkbook.Sheets("Übersicht").Activate
For i = 1 To Range("B65536").End(xlUp).Row
If Range("B" & i).Value Like "*.09.*" Then ThisWorkbook.Sheets_("September").Range("B65536").End(xlUp).Offset(1, 0).Value = Range("B" &_ i).Value
Next
ThisWorkbook.Sheets("September").Columns(2).NumberFormatLocal = "TT.MM.JJ"
ThisWorkbook.Sheets("September").Columns("A:P").EntireColumn.AutoFit
For i = 1 To Range("B65536").End(xlUp).Row
If Range("B" & i).Value Like "*.10.*" Then ThisWorkbook.Sheets_("Oktober").Range("B65536").End(xlUp).Offset(1, 0).Value = Range("B" & i).Value
Next
ThisWorkbook.Sheets("Oktober").Columns(2).NumberFormatLocal = "TT.MM.JJ"
ThisWorkbook.Sheets("Oktober").Columns("A:P").EntireColumn.AutoFit
For i = 1 To Range("B65536").End(xlUp).Row
If Range("B" & i).Value Like "*.11.*" Then ThisWorkbook.Sheets_("November").Range("B65536").End(xlUp).Offset(1, 0).Value = Range("B" & i).Value
Next
ThisWorkbook.Sheets("November").Columns(2).NumberFormatLocal = "TT.MM.JJ"
ThisWorkbook.Sheets("November").Columns("A:P").EntireColumn.AutoFit
For i = 1 To Range("B65536").End(xlUp).Row
If Range("B" & i).Value Like "*.12.*" Then ThisWorkbook.Sheets_("Dezember").Range("B65536").End(xlUp).Offset(1, 0).Value = Range("B" & i).Value
Next
ThisWorkbook.Sheets("Dezember").Columns(2).NumberFormatLocal = "TT.MM.JJ"
ThisWorkbook.Sheets("Dezember").Columns("A:P").EntireColumn.AutoFit
ThisWorkbook.Sheets("Q-Meldungen").Activate
For i = 1 To Range("B65536").End(xlUp).Row
If Range("B" & i).Value Like "*.09.*" Then ThisWorkbook.Sheets_("September").Range("B65536").End(xlUp).Offset(1, 0).Value = Range("B" &_ i).Value
Next
ThisWorkbook.Sheets("September").Columns(2).NumberFormatLocal = "TT.MM.JJ"
ThisWorkbook.Sheets("September").Columns("A:P").EntireColumn.AutoFit
For i = 1 To Range("B65536").End(xlUp).Row
If Range("B" & i).Value Like "*.10.*" Then ThisWorkbook.Sheets_("Oktober").Range("B65536").End(xlUp).Offset(1, 0).Value = Range("B" & i).Value
Next
ThisWorkbook.Sheets("Oktober").Columns(2).NumberFormatLocal = "TT.MM.JJ"
ThisWorkbook.Sheets("Oktober").Columns("A:P").EntireColumn.AutoFit
For i = 1 To Range("B65536").End(xlUp).Row
If Range("B" & i).Value Like "*.11.*" Then ThisWorkbook.Sheets_("November").Range("B65536").End(xlUp).Offset(1, 0).Value = Range("B" & i).Value
Next
ThisWorkbook.Sheets("November").Columns(2).NumberFormatLocal = "TT.MM.JJ"
ThisWorkbook.Sheets("November").Columns("A:P").EntireColumn.AutoFit
For i = 1 To Range("B65536").End(xlUp).Row
If Range("B" & i).Value Like "*.12.*" Then ThisWorkbook.Sheets_("Dezember").Range("B65536").End(xlUp).Offset(1, 0).Value = Range("B" & i).Value
Next
ThisWorkbook.Sheets("Dezember").Columns(2).NumberFormatLocal = "TT.MM.JJ"
ThisWorkbook.Sheets("Dezember").Columns("A:P").EntireColumn.AutoFit
Sheets("Übersicht").Activate
End Sub
Anzeige