Select certain count of rows from bottom up to copy

[GeeWhiz7]

Hi folks,

Today I found a bug in my workbooks that only causes a problem sometimes.

I have a source worksheet that has stock index close price data on it. In the past I used a macro to retrieve that data by selecting the range/copy to a different worksheet.
Today I noticed that since I used dates to retrieve the original data, and those dates relate to actual days whether the stock index trades or not, sometimes my retrieval gets a day or two more or less than other days.

I've been trying some different ideas to get the result I want, but it's not quite working so I'm sure someone here has an idea that will work better.

Here is a synopsis of the data. Source data (sometimes longer/shorter number of "trading" days in the same time period) and my desired result.
In my VBA , how can I only .Select and copy the last 20 entries from the last date (1/3/2022 shown) to copy over to the desired results sheet?

Thanks for your help! -Will

Book2.xlsx
	A	B	C	D	E	F	G	H
1	Source Data Sheet				Desired Result Sheet
2	Range Name				*Select only last 20 entries from the
3	_Dates	_Price			source data sheet
4
5	Dates	Price			Dates	Price
6	12/1/2021	4513.04			12/6/2021	4591.67
7	12/2/2021	4577.1			12/7/2021	4686.75
8	12/3/2021	4538.43			12/8/2021	4701.21
9	12/6/2021	4591.67			12/9/2021	4667.45
10	12/7/2021	4686.75			12/10/2021	4712.02
11	12/8/2021	4701.21			12/13/2021	4668.97
12	12/9/2021	4667.45			12/14/2021	4634.09
13	12/10/2021	4712.02			12/15/2021	4709.85
14	12/13/2021	4668.97			12/16/2021	4668.67
15	12/14/2021	4634.09			12/17/2021	4620.64
16	12/15/2021	4709.85			12/20/2021	4568.02
17	12/16/2021	4668.67			12/21/2021	4649.23
18	12/17/2021	4620.64			12/22/2021	4696.56
19	12/20/2021	4568.02			12/23/2021	4725.79
20	12/21/2021	4649.23			12/27/2021	4791.19
21	12/22/2021	4696.56			12/28/2021	4786.35
22	12/23/2021	4725.79			12/29/2021	4793.06
23	12/27/2021	4791.19			12/30/2021	4778.73
24	12/28/2021	4786.35			12/31/2021	4766.18
25	12/29/2021	4793.06			1/3/2022	4796.56
26	12/30/2021	4778.73
27	12/31/2021	4766.18
28	1/3/2022	4796.56
29
30
Sheet1

 

[My Aswer Is This]

Modifying your script to look like this:

Sub My_Script()
'Modified  1/4/2022  11:35:46 PM  EST
Dim lRow As Long
 lRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    Select Case lRow
    
        Case 1259
            Range("A2:AY1259").Copy
            Windows("Destination.xlsb").Sheets("Data").Range("B16").PasteSpecial Paste:=xlPasteValues

        Case 1260
            Range("A3:AY1260").Copy
            Windows("Destination.xlsb").Sheets("Data").Range("B16").PasteSpecial Paste:=xlPasteValues
    
        Case 1261
            Range("A4:AY1261").Copy
            Windows("Destination.xlsb").Sheets("Data").Range("B16").PasteSpecial Paste:=xlPasteValues
    
    End Select
End Sub

 

[GeeWhiz7]

Hey Will,
I think My Aswer Is This was pretty close with the Offset and Resize; just need to add the columns out to AY (ie, Column 51)...

Cells(Rows.Count, "A").End(xlUp).Offset(-1257).Resize(1258, 51).Copy _
    Destination:=Workbooks("Destination.xlsb").Sheets("Data").Range("B16")

Cheers,

Tony

Thanks Tony and My Answer is This. Both solutions provide me with examples that will work in different situations.
For this particular case, the 1-2 line solution works well and will allow me to set variables the user can change for how much offset gets applied.
Thanks for your help!