PDA

View Full Version : Copy Used Row



joshua1990
01-18-2017, 09:08 AM
Hello community!

I hope my knowledge of English is enough to describe the problem adequately.

I want to copy a defined range from one table to another table. So far I have always determined the area to be copied.
Now I would like, however, that the respective column always selects and copies to the last line of the table.

My previous approach is as follows:


Sub CopyToS()

Sheets("tbl_e").Range("A2:A400").copy Sheets("tbl_s").Range("A2:A400")
Sheets("tbl_e").Range("C2:I400").copy Sheets("tbl_s").Range("B2:H400")
Sheets("tbl_e").Range("J2:O400").copy Sheets("tbl_s").Range("J2:O400")
Sheets("tbl_e").Range("P2:P400").copy Sheets("tbl_s").Range("R2:R400")
Rows("1:400").EntireRow.AutoFit

End Sub


I know it's a simple problem, however, my VBA knowledge is not enough to solve that.


Best Regards
Joshua

Kenneth Hobs
01-18-2017, 11:41 AM
Welcome to the forum!

Dim lr&
With Sheets("tbl_s")
lr = Sheets("tbl_e").Cells(.Rows.Count, "A").End(xlUp).Row
Sheets("tbl_e").Range("A2:A" & lr).Copy .Range("A2")
Sheets("tbl_e").Range("C2:I" & lr).Copy .Range("B2")
Sheets("tbl_e").Range("J2:O" & lr).Copy .Range("J2")
Sheets("tbl_e").Range("P2:P" & lr).Copy .Range("R2")

.Rows("1:" & lr).EntireRow.AutoFit
End With
End Sub