Macro to read most recent row of data [Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hi,

I'd to create an Excel macro to read the last line of a spreadsheet with data and transfer that data to another worksheet in the same workbook. For instance, each month a new line of information will be entered on Worksheet A. I'd like Worksheet B to read the newest line on A (when a button with macro is pushed) so that B is ready with current month's info. Can anyone assist? Thanks!!

5 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
You did not mention where the data needs to go on other sheet.

Assuming the on the last row, column A is always populated, and row is to be copied on the same row on other sheet then

Dim lMaxRows as long

lMaxRows = Cells(Rows.count, "A").end(xlup).row
sheets("Worksheet B").range(lMaxRows & ":" & lMaxRows ) = sheets("Worksheet A").range(lMaxRows & ":" & lMaxRows ).value
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Thanks so much for the speedy answer rizvisa1. That code works, but as you pointed out I was a big vague - let me be more specific. Sheet A will have a new line added each month with 10 pieces (columns) of info in each row. Sheet B needs to be a summary of specific info from the newest month of Sheet A. I have 2 different scenario needs for Sheet B:
1. Info from Sheet A (ie row 20), columns A, C& E (20A, 20C & 20E) onto Sheet B row 20. Next month row references for both Sheet A & B would reference row 21
2. Info from Sheet A (ie row 20), columns A, C& E (20A, 20C & 20E) onto Sheet B row 1, but ALWAYS row 1, where Sheet B row 1 gets overwritten each month so as just to report on current month

Thanks :)
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
Could you please upload a sample file on some shared site like https://authentification.site and post back here the link to allow better understanding of how it is now and how you foresee.
Thanks for your time, much appreciated. Here are the 2 samples I created just for illustration:
https://authentification.site/files/22058909/sample_1.xls
https://authentification.site/files/22058910/sample_2.xls
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
Dim lMaxRows As Long

lMaxRows = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

Sheets("Summary").Range("D1") = Sheets("Sheet1").Range("A" & lMaxRows).Value
Sheets("Summary").Range("A4") = Sheets("Sheet1").Range("D" & lMaxRows).Value

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!