Copy last 13 items

  • Thread starter Thread starter Robert Gillard
  • Start date Start date
R

Robert Gillard

I have a worksheet which details a history of entries in 4 columns going
back many years. At the start of the worksheet is a summary of the last 13
months entries.

At the moment each month I copy the last 13 months entries and paste them
into the summary. I would like to have a formula that does this for me, but
do not know how to automatically capture the last 13 items of the column,
which as you appreciate will keep changing each month.

Any help appreciated.

Bob
 
Robert Gillard said:
I have a worksheet which details a history of entries in 4 columns going
back many years. At the start of the worksheet is a summary of the last 13
months entries.

At the moment each month I copy the last 13 months entries and paste them
into the summary. I would like to have a formula that does this for me, but
do not know how to automatically capture the last 13 items of the column,
which as you appreciate will keep changing each month.

Any help appreciated.

Bob
Click to expand...

To get 13 individual values (rather than, say, the sum of them) you will
need 13 formulas.

If your column (say A) just contains numeric data without embedded blanks,
the last one can be found using
=OFFSET(A1,COUNT(A:A)-1,0)
You may need to slightly adjust this if you have a header row or rows (or
anything similar). This is not difficult - all it's doing is to count how
many numbers are in column A (e.g. 43) and take the last by going down 42
rows from A1.

For the next-to-last, just put 2 instead of 1:
=OFFSET(A1,COUNT(A:A)-2,0)
and so on.
 
try
Sub copy13()
x = Cells(65536, 1).End(xlUp).Row
Range(Cells(x - 12, 1), Cells(x, 1)).Copy [b20]
End Sub
 
Back
Top