Copy Last 10 Values in a Column

[excelhelporfavor]

Hi Excel Guru's, another request for your expertise!

I have a spreadsheet with a column of values. This column of numbers grows longer every day as new data is added. I need a macro that will copy just the last/bottom 10 cells or values in that column, so that I can throw it into a graph to show the latest figures daily. Obviously it would have to continuously update to find the last/bottom 10 values as the list regularly grows.

I feel this should be an easy solution, but just can't figure it out at the moment. Any help is appreciated, thanks!

[Arkadi]

Hi excelheporfavor,

you can establish the last line in the column, then do a loop for last 10 to copy.....

find last row:

Please Login or Register  to view this content.

then:

Please Login or Register  to view this content.

[jolivanes]

Or

Please Login or Register  to view this content.

[Arkadi]

jolivanes,

Showoff :p

haha just kidding, much more sophisticated approach Nicely done.

[nathansav]

You dont need VBA, you can do this with formula, will the 10 cells info always be in the same place for the chart?

Please Login or Register  to view this content.

etc.etc.

[excelhelporfavor]

Perfection jolivanes...thanks to everyone who provided a solution!

[jolivanes]

I know you marked it solved but you can reference as follows also.
No need to copy/paste cell then.

(From Tushar Metha's site)

Cell E1 has the number of the last amount (in your case 10) to be shown in the graph. The reference to cell E1 can be replaced by the number 10 of course.
Column A starts at row 5 and has the Y values and column B also starts at row 5 and has the X values. (Change as required)

Xvalues=OFFSET('Sheet1'!$A$5,COUNTA('Sheet1'!$B:$B)-'Sheet1'!$E$1,0,'Sheet1'!$E$1,1)
YValues=OFFSET('Sheet1'!XValues,0,1)