AW: Aufgelistete Blätter durchsuchen
07.02.2005 14:16:19
Eleni
Hi UweD,
Danke fürs Makro, aber es macht irgendwie nix. Hier dein Code mit meinem Suchmakro:
Sub listen()
Dim SP%, LR%, i%, TB
SP = 1 ' Werte stehen in Spalte A
LR = Sheets("Übersicht_1").Cells(Rows.Count, SP).End(xlUp).Row 'letzte Zeile der Spalte
For i = 1 To LR
TB = Sheets("Übersicht_1").Cells(i, SP).Value ' TabellenName Lesen
If TB <> "" Then
On Error GoTo Fehler
ThisWorkbook.Sheets("Übersicht").Activate
Dim myWs As Worksheet, ws As Worksheet
Dim lng As Long, zei As Long
Dim n
Set ws = Worksheets("Übersicht")
With ws
zei = 3
If myWs.Name <> ws.Name And myWs.Name <> "Abkürzungsverzeichnis" And myWs.Name <> "Vorlage" Then
lng = myWs.[c65536].End(xlUp).Row
For n = 3 To lng Step 10
myWs.Range("A3").Copy
ws.Range("A" & zei).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
myWs.Range("B3").Copy
ws.Range("B" & zei).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
myWs.Range("S" & n & ":S" & n).Copy
ws.Range("D" & zei).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
myWs.Range("C" & n & ":C" & n).Copy
ws.Range("E" & zei).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
myWs.Range("J" & n & ":J" & n).Copy
ws.Range("F" & zei).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
myWs.Range("T" & n & ":T" & n).Copy
ws.Range("G" & zei).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
myWs.Range("U" & n & ":U" & n).Copy
ws.Range("H" & zei).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
myWs.Range("V" & n & ":V" & n).Copy
ws.Range("I" & zei).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
myWs.Range("W" & n & ":W" & n).Copy
ws.Range("J" & zei).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
myWs.Range("X" & n & ":X" & n).Copy
ws.Range("K" & zei).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
myWs.Range("Y" & n & ":Y" & n).Copy
ws.Range("L" & zei).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
zei = zei + 1
Next n
End If
End With
ThisWorkbook.Sheets("Übersicht").Columns("A:F").EntireColumn.AutoFit
ThisWorkbook.Sheets("Übersicht").Columns("K:L").EntireColumn.AutoFit
End If
Next
Exit Sub
Fehler:
If Err.Number = 9 Then
MsgBox "Tabelle '" & TB & "' nicht vorhanden"
Err.Clear
End If
Resume Next
End Sub
Ciao, Eleni