Select certain count of rows from bottom up to copy

G

GeeWhiz7

Board Regular
Joined
Nov 22, 2021
Messages
214
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Assuming you want to copy the entire last 20 rows
To a sheet named "Desired"
Modify name if needed.
Try this:
VBA Code: 
Sub Copy_Range()
'Modified  1/4/2022  4:22:02 PM  EST
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Rows(Lastrow).Offset(-19).Resize(20).Copy Sheets("Desired").Rows(2)
Application.ScreenUpdating = True
End Sub
 
Upvote 0
GeeWhiz7 said:
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
Click to expand...
The data that I need to copy is a range so I have tried to use End(xlUp) to count the rows and then count back the number of rows I need total, but I can't get the Range.Select syntax to work...
Something like below which throws and error.

VBA Code: 
Dim lRow As Long
Dim BackRows As Long

Windows("SourceSheet.xlsb").Activate
    Sheets("Adjusted Close Price").Select
   
'count the rows
    lRow = Cells(Rows.Count, 1).End(xlUp).row

'count back to starting point for selection
    BackRows = lRow - 1258
    Range("A2 & BackRows:"AY & lRow").Select

    Selection.Copy
    Windows("Destination.xlsb").Activate
    Sheets("Data").Select
    Range("B16").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
Upvote 0
My Aswer Is This said:
Assuming you want to copy the entire last 20 rows
To a sheet named "Desired"
Modify name if needed.
Try this:
VBA Code: 
Sub Copy_Range()
'Modified  1/4/2022  4:22:02 PM  EST
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Rows(Lastrow).Offset(-19).Resize(20).Copy Sheets("Desired").Rows(2)
Application.ScreenUpdating = True
End Sub
Click to expand...
Thanks My Answer is This, that was roughly my first approach (subtracting from the row count...I like your offset better), but then I realized I need to copy a range of data and I can't figure out how to get it to work. My second post shows what I'm stuck on now. The range.Select syntax doesn't quite work.
 
Upvote 0
You said in last post:
I need to copy a range of data (A2:AY1258)
Well that is more then 20 rows.
That looks like 1256 Rows

And earlier you said:
how can I only .Select and copy the last 20 entries
So I'm confused
 
Upvote 0
My Aswer Is This said:
You said in last post:
I need to copy a range of data (A2:AY1258)
Well that is more then 20 rows.
That looks like 1256 Rows

And earlier you said:
how can I only .Select and copy the last 20 entries
So I'm confused
Click to expand...
Sorry for the confusion. The original workbook is large and has many sheets so I was trying to keep it simple and use the example mini-sheet.

I guess the bottom line is for a given array, lets say starting in Column A and ending in Column AY with data always starting and ending in same rows no matter the column, I need to:
  • Calculate the last row (call it lRow)
  • Calculate (or Offset) a certain number of rows back from the last row (call it BackRow). This happens to be 1258 in reality and 20 in my simple mini-sheet.
  • Select the array that corresponds to A BackRow : AY iRow
The rest I can manage, its just the array I can't quite work out. Since the last row can change (some days its 1258 trading days, sometimes 1259 or 1260 for the same Total Real days for example) I need to start there and only select a total of 1258 days since where the data ultimately ends up needs exactly 1258 trading days of data, no more, no less.

Hopefully that helps.
 
Upvote 0
This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.
 
Upvote 0
GeeWhiz7 said:
Sorry for the confusion. The original workbook is large and has many sheets so I was trying to keep it simple and use the example mini-sheet.

I guess the bottom line is for a given array, lets say starting in Column A and ending in Column AY with data always starting and ending in same rows no matter the column, I need to:
  • Calculate the last row (call it lRow)
  • Calculate (or Offset) a certain number of rows back from the last row (call it BackRow). This happens to be 1258 in reality and 20 in my simple mini-sheet.
  • Select the array that corresponds to A BackRow : AY iRow
The rest I can manage, its just the array I can't quite work out. Since the last row can change (some days its 1258 trading days, sometimes 1259 or 1260 for the same Total Real days for example) I need to start there and only select a total of 1258 days since where the data ultimately ends up needs exactly 1258 trading days of data, no more, no less.

Hopefully that helps.
Click to expand...
If figured out a different way that in this case works.

Since I know that when the data is longer it is by 1, 2 or max 3 days, I used an ElseIf to deal with each case separately. Not that elegant, but solves the issue.

VBA Code: 
 lRow = Cells(Rows.Count, 1).End(xlUp).row
    
    If lRow = 1259 Then
        Range("A2:AY1259").Select
        Selection.Copy
        Windows("Destination.xlsb").Activate
        Sheets("Data").Select
        Range("B16").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    ElseIf lRow = 1260 Then
    
        Range("A3:AY1260").Select
        Selection.Copy
        Windows("Destination.xlsb").Activate
        Sheets("Data").Select
        Range("B16").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
     ElseIf lRow = 1261 Then
    
        Range("A4:AY1261").Select
        Selection.Copy
        Windows("Destination.xlsb").Activate
        Sheets("Data").Select
        Range("B16").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    End If
 
Upvote 0
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
 
Upvote 0
Solution
tonyyy said:
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
Click to expand...
Hi Tony,
I got pretty close to this code but missed Rows.Count in the Cells() and couldn’t getthe whole thing working. Let me replace my elseif and give yours a try and will reply in the morning. Thanks!
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Automatic copy new rows in sheet. Shooter contest.
Replies
8
Views
235
NdNoviceHlp
NdNoviceHlp
S
  • Question Question
List all staff names that worked under a manager on a certain date
Replies
5
Views
328
Fluff
Fluff
G
  • Locked
  • Question Question
VBA Userform - Change from 1900 Date Format to Long Date
Replies
1
Views
51
Peter_SSs
Peter_SSs
D
  • Solved
VBA to copy only matching data from 2 csv workbooks into the "MasterCSV"
2
Replies
18
Views
892
Peter_SSs
Peter_SSs
D
  • Locked
  • Question Question
VBA to copy only matching data from 2 csv workbooks into the "Master Workbook"
Replies
1
Views
324
RoryA
RoryA

Forum statistics

Threads
1,223,702
Messages
6,173,961
Members
452,539
Latest member
delvey

Share this page

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
Back
Top