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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,230
Office Version
  1. 2013
Platform
  1. Windows
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
 

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

GeeWhiz7

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

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,230
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
 

GeeWhiz7

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

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,230
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.
 

GeeWhiz7

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

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,771
Office Version
  1. 2010
Platform
  1. Windows
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
 
Solution

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
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!
 
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,686
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