Variable aktualisieren

Dim vntSh As Variant Dim Index2 As Integer Dim LetzteZelle As Long vntSh = Array(s1b, s1c, s1d, s1e) LetzteZelle = IIf(IsEmpty(Sheets(s1f).Cells(Rows.Count, 2)), _ Cells(Rows.Count, 2).End(xlUp).Row, Rows.Count) For Index2 = 0 To UBound(vntSh) With Sheets(vntSh(Index2)) .Range("A1:I" & .Cells(Rows.Count, 8).End(xlUp).Row). _ Copy Sheets(s1f).Cells(LetzteZelle + 1, 1) End With Next

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Benutzer

With Sheets(vntSh(Index2)) LetzteZelle = IIf(IsEmpty(Sheets(s1f).Cells(Rows.Count, 2)), _ Cells(Rows.Count, 2).End(xlUp).Row, Rows.Count) .Range("A1:I" & .Cells(Rows.Count, 8).End(xlUp).Row). _ Copy Sheets(s1f).Cells(LetzteZelle + 1, 1) End With

For Index2 = 0 To UBound(vntSh) With Sheets(vntSh(Index2)) LetzteZelle = IIf(IsEmpty(Sheets(vntSh(Index2)).Cells(Rows.Count, 2)), _ Cells(Rows.Count, 2).End(xlUp).Row, Rows.Count) .Range("a1").Copy Sheets(vntSh(Index2)).Range("A2:A" & LetzteZelle) End With Next

Hallo Holger,
schau dir mal dieser beiden Varianten an:


Option Explicit
Sub xxx1()
Dim s1b As String, s1c As String, s1d As String, s1e As String, s1f As String
Dim vntSh As Variant
Dim Index2 As Integer
Dim LetzteZelle As Long
vntSh = Array(s1b, s1c, s1d, s1e)
For Index2 = 0 To UBound(vntSh)
With Sheets(vntSh(Index2))
LetzteZelle = IIf(IsEmpty(Sheets(s1f).Cells(Rows.Count, 2)), _
Sheets(s1f).Cells(Rows.Count, 2).End(xlUp).Row, Rows.Count)
.Range("A1:I" & .Cells(Rows.Count, 8).End(xlUp).Row). _
Copy Sheets(s1f).Cells(LetzteZelle + 1, 1)
End With
Next
End Sub
Sub xxx2()
Dim s1b As String, s1c As String, s1d As String, s1e As String, s1f As String
Dim vntSh As Variant
Dim Index2 As Integer
Dim lngZ As Long, lngQ As Long
vntSh = Array(s1b, s1c, s1d, s1e)
With Sheets(s1f)                                   ' belegte Zeilen im Zielblatt
lngZ = IIf(IsEmpty(.Cells(Rows.Count, 2)), _
.Cells(Rows.Count, 2).End(xlUp).Row, Rows.Count)
End With
For Index2 = 0 To UBound(vntSh)
With Sheets(vntSh(Index2))
lngQ = IIf(IsEmpty(.Cells(Rows.Count, 8)), _
.Cells(Rows.Count, 8).End(xlUp).Row, Rows.Count) ' Zeilen im Quellblatt
If lngZ + lngQ > .Rows.Count Then
MsgBox "Quellblatt " & .Name & " passt nicht mehr in Zielblatt"
Exit Sub
End If
.Cells(1, 1).Resize(lngQ).Copy Sheets(s1f).Cells(lngZ + 1, 1)
lngZ = lngZ + lngQ
End With
Next
End Sub

Kommst du damit klar?
Wofür steht bei dir die Variable "LetzteZelle"?
Für die letzte Zeile des jeweiligen Quellblatts oder für die letzte Zeile des Zielblatts?
Das sollte immer klar sein!
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

Dim s1b$, s1c$, s1d$, s1e$ Dim vntSh As Variant Dim Index2 As Integer Dim LetzteZelle As Long vntSh = Array(s1b, s1c, s1d, s1e) For Index2 = 0 To UBound(vntSh) With Sheets(vntSh(Index2)) LetzteZelle = IIf(IsEmpty(Sheets(vntSh(Index2)).Cells(Rows.Count, 2)), _ Cells(Rows.Count, 2).End(xlUp).Row, Rows.Count) .Range("a1").Copy Sheets(vntSh(Index2)).Range("A2:A" & LetzteZelle) End With Next

Beliebteste Forumthreads (12 Monate)

10 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

10
Beiträge zum Forumthread

Beiträge zu diesem Forumthread