VBA code - Copy Range with last row used

[Ostoop]

I currently have the code below which does all I want except for one thing. Currently it copes a range of data in "Data" sheet and pastes it as values in sheet "JE" starting at cell C22:K22.

Sub CopyData()
    Sheets("Data").Range("B2:J17").Copy
    Sheets("JE").Range("C22").PasteSpecial xlPasteValues
End Sub

Cell B2 in the range is always the same, also column J, however row 17 can differ, the range should be up to the last row used based in column B.
So range can be B2:J10 or B2:B200 depending on how many rows exist.

Any ideas?

Thank you!

 

[steve the fish]

Is the data always being pasted into JE!C22? ie being overwritten each time?

 

[Ostoop]

Is the data always being pasted into JE!C22? ie being overwritten each time?

That's correct, data copied from "Data" sheet is always pasted into JE!C22. Each time the macro will be run an empty JE template will be used.

 

[steve the fish]

Try this

Sub CopyData()
Dim a As Integer
    a = Sheets("Data").Range("B" & Rows.Count).End(xlUp).Row
    Sheets("Data").Range("B2:J" & a).Copy
    Sheets("JE").Range("C22").PasteSpecial xlPasteValues
End Sub

 

[Ostoop]

Try this

Sub CopyData()
Dim a As Integer
    a = Sheets("Data").Range("B" & Rows.Count).End(xlUp).Row
    Sheets("Data").Range("B2:J" & a).Copy
    Sheets("JE").Range("C22").PasteSpecial xlPasteValues
End Sub

Great! It works thank you

 

[reinsermat]

Try this

Sub CopyData()
Dim a As Integer
    a = Sheets("Data").Range("B" & Rows.Count).End(xlUp).Row
    Sheets("Data").Range("B2:J" & a).Copy
    Sheets("JE").Range("C22").PasteSpecial xlPasteValues
End Sub

Thank you!