Copy Used Row

[joshua1990]

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]

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