Find last row, copy cell & paste to another worksheet

fathead

New Member
Joined
Sep 22, 2009
Messages
13
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.
 
Last edited:

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
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

New Member
Joined
Sep 22, 2009
Messages
13
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

Legend
Joined
Jun 19, 2002
Messages
63,650
Sorry about the typo.

Try

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,725
Messages
5,626,515
Members
416,187
Latest member
L_D18

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top