Select certain count of rows from bottom up to copy

GeeWhiz7

Board Regular
Joined
Nov 22, 2021
Messages
60
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
ABCDEFGH
1Source Data SheetDesired Result Sheet
2Range Name*Select only last 20 entries from the
3_Dates_Pricesource data sheet
4
5DatesPriceDatesPrice
612/1/20214513.0412/6/20214591.67
712/2/20214577.112/7/20214686.75
812/3/20214538.4312/8/20214701.21
912/6/20214591.6712/9/20214667.45
1012/7/20214686.7512/10/20214712.02
1112/8/20214701.2112/13/20214668.97
1212/9/20214667.4512/14/20214634.09
1312/10/20214712.0212/15/20214709.85
1412/13/20214668.9712/16/20214668.67
1512/14/20214634.0912/17/20214620.64
1612/15/20214709.8512/20/20214568.02
1712/16/20214668.6712/21/20214649.23
1812/17/20214620.6412/22/20214696.56
1912/20/20214568.0212/23/20214725.79
2012/21/20214649.2312/27/20214791.19
2112/22/20214696.5612/28/20214786.35
2212/23/20214725.7912/29/20214793.06
2312/27/20214791.1912/30/20214778.73
2412/28/20214786.3512/31/20214766.18
2512/29/20214793.061/3/20224796.56
2612/30/20214778.73
2712/31/20214766.18
281/3/20224796.56
29
30
Sheet1
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,230
Office Version
  1. 2013
Platform
  1. Windows
Modifying your script to look like this:
VBA Code:
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

GeeWhiz7

Board Regular
Joined
Nov 22, 2021
Messages
60
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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)...

VBA Code:
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!
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,492
Messages
5,764,689
Members
425,230
Latest member
DzOus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top