AW: Bereich zwischen zwei Namen markieren
05.05.2012 11:01:11
Tino
Hallo,
hier mal eine Variante.
Die Datei müsstest Du als .xlsm speichern, .xlsx kann kein VBA!
Evtl. bei With Tabelle1.UsedRange.Columns(1).Cells die Tabelle und Spalte anpassen.
Sub Start()
Dim rngCopy As Range
Dim n&, nErste&, nLetzte&
Dim objTab As Worksheet
Const intColorIntex = 15
With Tabelle1.UsedRange.Columns(1).Cells
For n = 1 To .Count
If nErste = 0 Then
If .Cells(n, 1).Interior.ColorIndex = intColorIntex Then
nErste = n
End If
ElseIf (nLetzte = 0) Or (n = .Count) Then
If (.Cells(n, 1).Interior.ColorIndex = intColorIntex) Or (n = .Count) Then
nLetzte = n - IIf(n = .Count, 0, 1)
Set rngCopy = Range(.Cells(nErste, 1), .Cells(nLetzte, 1)).EntireRow
nErste = n
nLetzte = 0
End If
End If
If Not rngCopy Is Nothing Then
With ThisWorkbook
If CheckTab(Trim$(rngCopy.Cells(1, 1))) Then
Set objTab = .Sheets(Trim$(rngCopy.Cells(1, 1)))
objTab.UsedRange.Clear
Else
Set objTab = .Sheets.Add(After:=.Sheets(.Sheets.Count))
objTab.Name = Trim$(rngCopy.Cells(1, 1))
End If
End With
rngCopy.Copy objTab.Cells(1, 1)
Set rngCopy = Nothing
End If
Next n
End With
End Sub
Function CheckTab(strName$) As Boolean
On Error Resume Next
CheckTab = ThisWorkbook.Sheets(strName).Index <> 0
End Function
Gruß Tino