AW: Liste aus Tabelle generien VBA
09.12.2016 10:16:55
UweD
Hallo
meinst du das so?
Sub Mediacontrol()
On Error GoTo Fehler
Application.ScreenUpdating = False
Dim TB1, TB2, Sp As Integer
Dim RR As Long, LR As Long, i As Long, ZZ As Long
Set TB1 = Sheets("Sheet1")
Set TB2 = Sheets("Sheet2")
Sp = 1 'Spalte A
'Reset
With TB2
RR = .Cells.SpecialCells(xlCellTypeLastCell).Row 'Letzte Zeile des gesamten Blattes
.Rows("5:" & RR).Delete xlUp
End With
LR = TB1.Cells(TB1.Rows.Count, Sp).End(xlUp).Row 'letzte Zeile der Spalte
ZZ = 4
For i = 4 To LR
TB2.Cells(ZZ - 1, 3) = TB1.Cells(i, 1)
TB1.Range(TB1.Cells(i, 2), TB1.Cells(i, 14)).Copy
TB2.Cells(ZZ, 3).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
If i < LR Then
TB2.Rows("2:4").Copy TB2.Rows(ZZ + 1)
ZZ = ZZ + 3
End If
Next
Fehler:
If Err.Number <> 0 Then MsgBox "Fehler: " & _
Err.Number & vbLf & Err.Description: Err.Clear
End Sub
UweD