makro einfacher gestalten

Question

Ich möchte folgendes Makro einfacher gestalten



Range("B" & Cells(Rows.Count, 2).End(xlUp).Row).Select

Selection.copy
Sheets("Wiegeschein").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Daten").Select
Application.CutCopyMode = False


Range("C" & Cells(Rows.Count, 2).End(xlUp).Row).Select

Selection.copy
Sheets("Wiegeschein").Select
Range("B1").Select
ActiveSheet.Paste
Sheets("Daten").Select
Application.CutCopyMode = False


Range("D" & Cells(Rows.Count, 2).End(xlUp).Row).Select

Selection.copy
Sheets("Wiegeschein").Select
Range("C1").Select
ActiveSheet.Paste
Sheets("Daten").Select
Application.CutCopyMode = False

Answer 1

hallo,

eine Frage:

ist in allen Spalten die Zeilenanzahl gleich?

Dann müsste es so gehen

Range("B" & Cells(Rows.Count, 2).End(xlUp).Row & ":D" & Cells(Rows.Count, 2).End(xlUp).Row).Select

Selection.copy
Sheets("Wiegeschein").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Daten").Select
Application.CutCopyMode = False

Gruß


Helmut

Answer 2

hi all ^^

ein beispiel variabler zeilenanzahl

gruss nighty

Sub copy_spalte()
With Worksheets(1)
.Range("B2:B" & .Range(.Cells(Rows.Count, 2), .Cells(Rows.Count, 2)).End(xlUp).Row).Copy
Worksheets(2).Range("B" & Worksheets(2).Range(Worksheets(2).Cells(Rows.Count, 2), Worksheets(2).Cells(Rows.Count, 2)).End(xlUp).Row + 1).PasteSpecial Paste:=xlValues, Operation:=xlNone
.Range("C2:C" & .Range(.Cells(Rows.Count, 3), .Cells(Rows.Count, 3)).End(xlUp).Row).Copy
Worksheets(2).Range("C" & Worksheets(2).Range(Worksheets(2).Cells(Rows.Count, 3), Worksheets(2).Cells(Rows.Count, 3)).End(xlUp).Row + 1).PasteSpecial Paste:=xlValues, Operation:=xlNone
.Range("D2:D" & .Range(.Cells(Rows.Count, 4), .Cells(Rows.Count, 4)).End(xlUp).Row).Copy
Worksheets(2).Range("D" & Worksheets(2).Range(Worksheets(2).Cells(Rows.Count, 4), Worksheets(2).Cells(Rows.Count, 4)).End(xlUp).Row + 1).PasteSpecial Paste:=xlValues, Operation:=xlNone
End With
Application.CutCopyMode = False
End Sub

Answer 3

hi all ^^

oder wenn alle drei bereiche gleiche zeilenanzahl haben

gruss nighty

Sub copy_spalte()
With Worksheets(1)
.Range("B2:D" & .Range(.Cells(Rows.Count, 2), .Cells(Rows.Count, 2)).End(xlUp).Row).Copy
Worksheets(2).Range("B" & Worksheets(2).Range(Worksheets(2).Cells(Rows.Count, 2), Worksheets(2).Cells(Rows.Count, 2)).End(xlUp).Row + 1).PasteSpecial Paste:=xlValues, Operation:=xlNone
End With
Application.CutCopyMode = False
End Sub