VBA Rolling data based on year/month

[nburaq]

Hi Gents,
I have a table and I need to enter the data manually every month. At the same time,to compare the data with other months, rows need to be moved upwards. So the oldest month entered in the table will dissappear and the newest month will be seen at the bottom. I believe this is only possible with VBA. I have added the data as sample and screenshots before and after. Thanks for your help!

Data:

Date	Location 1	Location 2	Location 3	Location 4	Location 5	Location 6	Location 7	Location 8
May/22​	1,1​	0,0​	0,3​	0,5​	0,0​	0,0​	0,0​	0,2​
Jun/22​	1,3​	4,5​	1,1​	1,8​	0,0​	0,8​	0,0​	0,0​
Jul/22​	1,4​	3,9​	1,6​	4,5​	0,0​	2,3​	0,0​	0,1​
Aug/22​	1,7​	0,0​	1,0​	1,6​	0,4​	0,0​	0,0​	20,9​
Sep/22​	2,3​	2,4​	0,0​	0,0​	6,8​	0,1​	0,0​	1,4​
Oct/22​	2,1​	1,2​	1,0​	1,1​	2,7​	0,0​	2,4​	0,6​
Nov/22​	1,9​	0,0​	0,8​	2,3​	0,0​	1,3​	0,5​	2,6​
Dec/22​	7,9​	2,3​	0,7​	1,9​	0,2​	5,5​	0,0​	0,0​
Jan/23​	7,0​	0,0​	1,2​	0,5​	0,2​	0,0​	0,0​	4,1​
Feb/23​	4,1​	0,0​	0,0​	0,7​	0,0​	0,0​	0,0​	1,4​
Mar/23​	4,2​	4,5​	1,4​	4,3​	0,0​	1,1​	0,0​	0,0​
Apr/23​	0,6​	3,0​	0,4​	1,2​	2,0​	0,0​	0,0​	0,8​

Data to be entered for May 23:

Enter New Values:

5,6​

2,3​

0,8​

1,9​

0,2​

5,5​

0,0​

0,0​

Before:

After:

 

[NateSC]

The macro isn't too complicated so you should be able to update as needed (maybe cell references would change if you move things around since this works off fixed cell locations).

Sub UpdateData()

Dim ws As Worksheet

    Set ws = ActiveSheet

    ws.Range("A2:I2").Delete shift:=xlShiftUp
   
    ws.Range("A13").Value = ws.Range("K1").Value
    ws.Range("A13").NumberFormat = "mmm/yy"
   
    ws.Range("B13:I13").Value = ws.Range("L2:S2").Value
    ws.Range("A13:I13").HorizontalAlignment = xlRight

End Sub

I added a formula in K1 to figure out the month and year if you want that. Otherwise, you can just type it in. Now, just make a form button and link it to the macro to run.

temp macro work.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Date	Location 1	Location 2	Location 3	Location 4	Location 5	Location 6	Location 7	Location 8		Jun/23
2	May/22	1,1	0,0	0,3	0,5	0,0	0,0	0,0	0,2		Enter New Values:	5,6	2,3	0,8	1,9	0,2	5,5	0,0	0,0
3	Jun/22	1,3	4,5	1,1	1,8	0,0	0,8	0,0	0,0
4	Jul/22	1,4	3,9	1,6	4,5	0,0	2,3	0,0	0,1
5	Aug/22	1,7	0,0	1,0	1,6	0,4	0,0	0,0	20,9
6	Sep/22	2,3	2,4	0,0	0,0	6,8	0,1	0,0	1,4
7	Oct/22	2,1	1,2	1,0	1,1	2,7	0,0	2,4	0,6
8	Nov/22	1,9	0,0	0,8	2,3	0,0	1,3	0,5	2,6
9	Dec/22	7,9	2,3	0,7	1,9	0,2	5,5	0,0	0,0
10	Jan/23	7,0	0,0	1,2	0,5	0,2	0,0	0,0	4,1
11	Feb/23	4,1	0,0	0,0	0,7	0,0	0,0	0,0	1,4
12	Mar/23	4,2	4,5	1,4	4,3	0,0	1,1	0,0	0,0
13	Apr/23	0,6	3,0	0,4	1,2	2,0	0,0	0,0	0,8
Sheet4

Cell Formulas
Range	Formula
K1	K1	=TEXT(TODAY(),"mmm/yy")

 

[nburaq]

Thanks for the solution. later on, I realized that there is no need for extensive vba coding. I could solve the problem with macro recorder.