VBA CODE TO PICK ONLY FIVE OBSERVATIONS IN SHEET1 AND DISPLAY IN SHEET2

[Mponda]

I have data set to put in sheet1 which has to be added new data after the year is complete. In this data set, I want to display only the last five observations on sheet2 with the newly added data inclusive.
The below is my sample data in which, the last five observations starting 2015 to 2020 have to be displayed on sheet2, and when the year 2021 is complete the display has to change to 2021 from 2016.
How can I write a VBA code that picks only the last observations regardless of the newly added observations?

year

1998​

1999​

2000​

2001​

2002​

2003​

2004​

2005​

2006​

2007​

2008​

2009​

2010​

2011​

2012​

2013​

2014​

2015​

2016​

2017​

2018​

2019​

2020​

x

2,632.60​

2,778.40​

2,958.60​

3,222.20​

3,453.50​

3,668.60​

3,876.40​

4,086.60​

4,341.20​

4,574.20​

4,813.90​

5,062.70​

5,350.00​

5,687.20​

5,969.50​

6,289.50​

6,685.40​

7,086.20​

7,566.00​

8,127.70​

8,703.50​

9,212.30​

9,722.50​

y

33.2​

58.4​

53.2​

81.8​

84.9​

108.3​

117.5​

120.7​

136.9​

159.6​

188.4​

162.6​

171.3​

189​

196​

215.1​

200.3​

203.5​

256.5​

329.2​

323.6​

340.9​

436.2​

 

[EXCEL MAX]

Hello MPonda,
you can try this...

Sub PickRange()

    Dim vWS1 As Worksheet, vWS2 As Worksheet
    Dim vC As Integer, vR As Long
    
    Set vWS1 = Sheets("Sheet1")
    Set vWS2 = Sheets("Sheet2")
    vC = vWS1.Cells(1, Columns.Count).End(xlToLeft).Column
    vR = vWS1.Cells(Rows.Count, "A").End(xlUp).Row
    vWS2.[A1].Resize(vR, 5).Value = _
    vWS1.Cells(1, vC).Offset(, -4).Resize(vR, 5).Value
    
End Sub

 

[lrobbo314]

Formula version.

Book1
	A	B	C	D	E
1	2016	2017	2018	2019	2020
2	7,566.00	8,127.70	8,703.50	9,212.30	9,722.50
3	256.5	329.2	323.6	340.9	436.2
Sheet2

Cell Formulas
Range	Formula
A1:E3	A1	=INDEX(Sheet1!1:1,1, AGGREGATE(14,6,(1/(Sheet1!1:1<>""))*COLUMN(Sheet1!1:1),6-COLUMNS(Sheet1!$A$1:A1)))

 

[Mponda]

Hello MPonda,
you can try this...

Sub PickRange()

    Dim vWS1 As Worksheet, vWS2 As Worksheet
    Dim vC As Integer, vR As Long
   
    Set vWS1 = Sheets("Sheet1")
    Set vWS2 = Sheets("Sheet2")
    vC = vWS1.Cells(1, Columns.Count).End(xlToLeft).Column
    vR = vWS1.Cells(Rows.Count, "A").End(xlUp).Row
    vWS2.[A1].Resize(vR, 5).Value = _
    vWS1.Cells(1, vC).Offset(, -4).Resize(vR, 5).Value
   
End Sub

Thank you very much. It is working very well and thank you once again for your help.

 

[EXCEL MAX]

I'm glad if this was helpful.