AW: Korrektur
23.01.2009 08:52:00
Tino
Hallo,
habe ausversehen den ersten Code genommen, ich gehe nochmal ins Bett. ;-)
Sub Makro1()
Dim Bereich As Range, myZ As Range
Dim LCol As Long, LColFomel As Long
Dim myTab1 As Worksheet, myTab2 As Worksheet
With Application
.ScreenUpdating = False
.EnableEvents = False
Set myTab1 = Sheets("Tabelle1") 'Deine Tabelle1
Set myTab2 = Sheets("Tabelle2") 'Deine Tabelle2
With myTab1
Set Bereich = .Range("M2", .Cells(.Cells.SpecialCells(xlCellTypeLastCell).Row, 13))
LColFomel = Bereich.Column
LCol = .Columns.Count - Bereich.Column
Set Bereich = Bereich.Offset(0, LCol)
Bereich.FormulaR1C1 = "=IF(COUNTIF(" & myTab2.Name & "!C" & LColFomel & ",RC" & LColFomel & ")=0,0,"""")"
On Error GoTo KeineZelle:
Set Bereich = .Columns(.Columns.Count).SpecialCells(xlCellTypeFormulas, 1)
With myTab2
For Each myZ In Bereich
myTab1.Range(myTab1.Cells(myZ.Row, LColFomel), myTab1.Cells(myZ.Row, LColFomel + 1)).Copy .Cells(.Rows.Count, LColFomel).End(xlUp).Offset(1, 0)
Next myZ
End With 'Sheets("Tabelle2")
KeineZelle:
.Columns(.Columns.Count).Delete
End With 'Sheets("Tabelle1")
.ScreenUpdating = True
.EnableEvents = True
End With 'Application
End Sub
Gruß Tino