Macro to read most recent row of data

Closed
jatwork - Apr 21, 2010 at 10:21 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Apr 21, 2010 at 05:39 PM
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 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 21, 2010 at 10:43 AM
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
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 :)
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 21, 2010 at 12:37 PM
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.
0
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
0

Didn't find the answer you are looking for?

Ask a question
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 21, 2010 at 05:39 PM
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
0