|
Option Explicit |
|
|
|
'1. Makro kopiert nur Spalte A nach A |
|
|
|
Private Sub cmdName_Click() |
|
Dim Tab2 As Object, Tab5 As Object |
|
Dim lngzeile As Long, lz As Long |
|
Dim EndAdr As String, Wert As Object |
|
Set Tab2 = Worksheets("Tabelle2") |
|
Set Tab5 = Worksheets("Tabelle5") |
|
|
|
lz = Tab5.Cells.Rows.Count |
|
'ermittle LastZell in Spalte A (beide Tabellen) |
|
lngzeile = Tab2.Cells(lz, 1).End(xlUp).Row |
|
EndAdr = Tab5.Cells(lz, 1).End(xlUp).Address |
|
|
|
lngzeile = lngzeile + 1 'next Zeile in Tab(2) |
|
|
|
'Schleife zum übertragen von Daten (Spalte A) |
|
For Each Wert In Tab5.Range("A1", EndAdr) |
|
Tab2.Cells(lngzeile, 1) = Wert.Value |
|
Next Wert |
|
End Sub |
|
|
|
|
|
'2. Makro kopiert mehrere Spalten von A nach A |
|
|
|
Private Sub cmdName_Click() |
|
Dim sp As Integer 'Anzahl Spalten |
|
Dim Tab2 As Object, Tab5 As Object |
|
Dim lngzeile As Long, lz As Long |
|
Dim EndAdr As String, Wert As Object |
|
Set Tab2 = Worksheets("Tabelle2") |
|
Set Tab5 = Worksheets("Tabelle5") |
|
|
|
lz = Tab5.Cells.Rows.Count |
|
'ermittle LastZell in Spalte A (beide Tabellen) |
|
lngzeile = Tab2.Cells(lz, 1).End(xlUp).Row |
|
EndAdr = Tab5.Cells(lz, 1).End(xlUp).Address |
|
|
|
lngzeile = lngzeile + 1 'next Zeile in Tab(2) |
|
|
|
'** Alternativ mit Copy + Resize (mehrere Spalten) |
|
'Schleife zum übertragen von Daten |
|
sp = 1 'Anzahl der zu kopierenden Spalten |
|
For Each Wert In Tab5.Range("A1", EndAdr) |
|
Wert.Resize(1, sp).Copy 'Spalten kopieren |
|
Tab2.Cells(lngzeile, 1).PasteSpecial xlValues |
|
Next Wert |
|
Application.CutCopyMode = False |
|
End Sub |