Macro to Copy Paste Value to next blank row

[luke1438]

I am trying to copy from sheet 1 the range C5:I5
and
paste Value it in the next available row column "B" in sheet 2

Any suggestions on how I can do this?

Thanks
Luke

 

[mirabeau]

Would this code do it?

I'm unsure just where you want it pasted, but easily modified as need be.

Sub copypaste()
Dim lrb As Long
With Sheets("Sheet2")
    lrb = .Range("B" & Rows.Count).End(3).Row
Sheets("sheet1").Range("C5:I5").Copy .Range("C" & lrb + 1)
End With
End Sub

 

[AlphaFrog]

Sub Macro1()
    Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(, 7).Value = _
        Sheets("Sheet1").Range("C5:I5").Value
End Sub

 

[luke1438]

Both work perfectly!

Thank you very much

Luke

 

[mirabeau]

Actually, I see now you just wanted the values pasted.

My code transferred the whole range (incl. formatting etc) which was more than needed.

A clear violation of William of Ockham's "razor" principle that "it's vain to do with more what can be done with fewer" etc.

Guess I'd just better read the post better next time.