Select varying row to copy to another sheet

[Hickmanyada]

I need a macro to copy data from one sheet to another. In sheet 2 i need to copy data from the same row in a range between columns C to H. The row will change to be the bottom row with data in it.(can a formula be in the cell and it not be recognised by this if it is blank? or am I asking too much). I will then do a special paste, values only into sheet 1, C5 to H5.

help is much appreciated.

 

[Stridhan]

Perhaps

Sub test()

Dim lr As Integer

lr = Sheets("Sheet2").Cells(Rows.Count, "C").End(xlUp).Row

Sheets("Sheet1").Range("C5:H5").Value = _
Sheets("Sheet2").Range("C" & lr, "H" & lr).Value
 
End Sub

 

[Hickmanyada]

Thank you, that worked at copying over the bottom line of data to where I wanted. I have formula input into the rows, is there anyway that it could ignore the rows with formula and go up to find cells with actual values. I tried blanking my formula but it still recognises it.