Reference the last 13 rows of data

[Robert Gillard]

At the moment each month a new row of data is added to the bottom of a
"RawData" sheet, this sheet holds monthly data from 2003.
A Summary sheet details just the last 13 months of data. At the moment, each
month I have to delete the top month and copy and paste the "new" months
data on the bottom.
I believe I can set a formula that will always select the last 13 rows of
data from the "RawData" sheet, I regret I do not know how to go about this,
can anybody help please.

With thanks

Bob

 

[Don Guillett]

And what would you like this formula to do?

 

[Ron de Bruin]

Hi Robert

Try this example for the activesheet

Copy both in a formula and run test
There is no error checking if there are less then 13 rows

Sub test()
range(Cells(LastRow(ActiveSheet) - 12, 1), Cells(LastRow(ActiveSheet), 1)).EntireRow.Select
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
after:=sh.range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

 

[Ron de Bruin]

Copy both in a formula and run test

Copy both in a module and run test

 

[Robert Gillard]

The Summary sheet should show the latest 13 months records, so I would like
the row A13:J13 on the Summary sheet to lookup the last row of the RawData
sheet and to reflect that data. Then I want row A12:J12 to lookup the 2nd
from last row on the Raw Data sheet etc through to A1:J1 which would lookup
the 13th from bottom row on the RawData sheet and reflect it on the Summary
sheet..

I am sorry my initial post was unclear.

Bob