how to extract most recent data from database

[bsurfn99]

I have a excel database that consists of text, numbers, and columns that are formula results. It grows by adding information on the following row. I would like to be able to extract the most recent information from it, let's say the last 5 rows and then paste that information into a different sheet. I'm having a hard time determining a way to find and then copy paste this information. Any ideas?

Thanks,
bsurfn99

 

[SkipVought]

Hi,

What is it that identifies the most recent rows?

Is there a DateStamp on the row?

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[Bong]

I think that to do what you want there has to be some logical way of determining "recent" from the data itself. When you query the database, what do you know about what has previously been extracted?

_________________
Bob Rashkin

 

[bsurfn99]

Thanks for your interest in helping.

I do not have a date stamp, but do have an index number in column 2. I am looking for the "last 5 entries" rather than saying "find everything within a certain time period."

I was planning on just modifying a loop process to find the last 5 filled rows, and then copy/paste it. Here is the start of my code...

' Find 1st empty cell 
    Let counter = 2
    Set curcell = Worksheets("database").Cells(counter, 2)
    Do Until curcell.Value = ""
        counter = counter - 5
        Set curcell = Worksheets("database").Cells(counter, 2)
    Loop

bsurfn99

 

[SkipVought]

lLastRow = Worksheets("database").[A1].currentregion.rows.count

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[bsurfn99]

skip,

I gave your code a spin, and I may not be putting it in it's correct context. your code counts the number of rows, but how can I use this info to extract the last 5 filled rows of information? If I change your code to .select, instead of .count, it captures the whole table which would be useful. Is there a way I can limit it selection?


bsurfn99

 

[SkipVought]

You have to know what the last row is, in order to get then LAST x rows.

with Worksheets("database")
lLastRow = .[A1].currentregion.rows.count
set Last5Rows = range(cells(lLastRow-4, 1), cells(lLastRow, .columns.count))
end with

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[bsurfn99]

Thanks Skip!