VBA to copy most recent data

[jalea148]

Col A has dates and Col B has values
Every day new data added to bottom row.
Want VBA to copy the most recent 50 rows to C1:C50, D1:D50

[JeteMc]

A couple of formulas would also fill in the data automatically. The formula to populate column C with the most recent 50 dates could be:

Please Login or Register  to view this content.

The formula to populate column D with the most recent 50 values could be:

Please Login or Register  to view this content.

Let us know if you have any questions.

[jalea148]

A couple of formulas would also fill in the data automatically. The formula to populate column C with the most recent 50 dates could be:

Please Login or Register  to view this content.

The formula to populate column D with the most recent 50 values could be:

Please Login or Register  to view this content.

Let us know if you have any questions.

Beautiful; thank you.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

[jalea148]

A couple of formulas would also fill in the data automatically. The formula to populate column C with the most recent 50 dates could be:

Please Login or Register  to view this content.

The formula to populate column D with the most recent 50 values could be:

Please Login or Register  to view this content.

Let us know if you have any questions.

Only 1 problem. My dates do not include weekends and holidays. Your formula tries to assign these dates - #N/A

[JeteMc]

Try this for the date (C1 and down):

Please Login or Register  to view this content.

And this for the value (D1 and down):

Please Login or Register  to view this content.

Let us know if you have any questions.

[JeteMc]

I forgot to account for holidays, so:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[jalea148]

I forgot to account for holidays, so:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

The only problem is the date on the bottom is not today - it is the largest

[JeteMc]

In that event I would think that it would be good to use the following formula to find the last date in column A:

Please Login or Register  to view this content.

Then reference the cell containing that formula, N1 in the attached sample, inside the WORKDAY function of both formulas, as in:

Please Login or Register  to view this content.

You could put the LOOKUP function in the formula, in place of N1, however that seems to slow the process down a lot, I believe that is because the formula in each of the 50 cells in looking to see the the last non-blank cell out of the one million plus rows in column A. As it is that only has to happen once. I hope that makes sense.
Let us know if you have any questions.

[jalea148]

I forgot to account for holidays, so:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Instead of your suggestion =LOOKUP(99^99,1/(A:A<>""),A:A) I used max(A:A). It's not important, the workday function did not know 9/5/11 was a holiday. Thanx for all your help and patience.

[JeteMc]

You're Welcome and I'm glad that you got a solution that works. I assume by "not important" that you have solved the holiday issue, and if so please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.