Find last row, copy cell & paste to another worksheet

[fathead]

Hi all,

Can anyone help with this one. I'm working between 2 worksheets, a list of data held in worksheet 'Store' and some empty cells in worksheet 'Input'.

I'm hoping to add some VBA code to the end of an existing macro to do the following:

Find the last cell/row in the worksheet 'Store', copy the contents from cells B & F, and then paste them into cells A6 & B6 on the worksheet 'Input'.

Hopefully this should mean as my 'Store' sheet is populated, the last row of the sheet is always displayed on the 'Input' sheet.

I'm using it as a kind of display back to user the last row input.

Thanks all


*EDIT* I should point out, column A on Sheet 'Store' is prepopulated to the end, so the find last cell forumla needs to check column B.

 

[VoG]

Try

With Sheets("Input")
    .Range("A6").Value = Sheets("Store").Range("B" & rws.Count).End(xlUp).Value
    .Range("B6").Value = Sheets("Store").Range("F" & rws.Count).End(xlUp).Value
End With

 

[fathead]

VOG,

Thats worked a treat, one minor adjustment (rws.count had to read rows.count). One additional thing, we've counted down column B in the 'Store' sheet which is great, now I also just need to copy the cell in column A in this newly identified last row, into cell E6 on 'Input'.

Hopefully that makes sense? Because Column A is already populated, I need to copy back the cell that is in the same row as the last input in cell B.

 

[VoG]

Sorry about the typo.

Try

Dim LR As Long
LR = Sheets("Store").Range("B" & Rows.Count).End(xlUp).Row
With Sheets("Input")
    .Range("A6").Value = Sheets("Store").Range("B" & LR).Value
    .Range("B6").Value = Sheets("Store").Range("F" & LR).Value
    .Range("E6").Value = Sheets("Store").Range("A" & LR).Value
End With

 

[fathead]

VOG,

Fantastic. Thank you very much - works a treat