VBA to put items in Chronological Order

[Glasgowsmile]

I'm trying to shift out old data when new data is added and then compare the dates to see where the new data should be placed.

Right now my big issue is the first If statement in the VBA. The worksheet has an IF to check those criteria and it's true by time I get to that part of the VBA code but that first IF statement isn't executing and I don't understand why.

Sub ImportChrono()
   
    NewBA = Sheet1.Range("B2").Value
    PBA = Sheet1.Range("D2").Value
    BA2 = Sheet1.Range("F2").Value
    BA3 = Sheet1.Range("H2").Value
        
        ' Clearing old data to make room for new data
        
        If Not IsEmpty(Sheet1.Range("A2, C2, E2, G2")) Then
            
            ' Delete the oldest record (3 records back)
            Sheet1.Range("G2:H4").ClearContents
        
            ' Copy 2 Records back to 3 Records Back
            Sheet1.Range("E2:F4").Copy Sheet1.Range("G2")
        
            ' Copy Previous Record to 2 Records Back
            Sheet1.Range("C2:D4").Copy Sheet1.Range("E2")
            
            ' Copy Current to Previous Record
            Sheet1.Range("A2:B4").Copy Sheet1.Range("C2")
            
            ' Add New Data
            Sheet1.Range("L2:M4").Copy Sheet1.Range("A2")
            
        End If

With Sheet1
    If NewBA > BA3 And NewBA > BA2 And NewBA < PBA Then
        .Range("A2:B4").Copy Destination:=.Range("Q2")
        .Range("C2:D2").Copy Destination:=.Range("A2")
        .Range("Q2:R4").Copy Destination:=.Range("C2")
    ElseIf NewBA > BA4 And NewBA > BA3 And NewBA <= BA2 And NewBA <= PBA Then
        .Range("L2:M4").Copy Destination:=.Range("E2")
    ElseIf NewBA > BA4 And NewBA <= BA3 And NewBA <= BA2 And NewBA <= PBA Then
        .Range("L2:M4").Copy Destination:=.Range("G2")
    End If
End With

End Sub

TestingChrono.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Current		Previous record		2 Records Back		3 Records Back
2	2/26/2022	3/1/2022	2/15/2022	2/25/2022	1/30/2022	2/14/2022	1/23/2022	1/29/2022				5/15/2022	2/27/2022
3	Col1	Col1	Col2	Col2	Col3	Col3	Col4	Col4				NEW DATA	NEW DATA
4	Col1	Col1	Col2	Col2	Col3	Col3	Col4	Col4				NEW DATA	NEW DATA
5
6		FALSE
7
Sheet1

Cell Formulas
Range	Formula
B6	B6	=IF(AND(B2>H2,B2>F2,B2<D2),TRUE,FALSE)

 

[Glasgowsmile]

It have probably failed to explain this correctly. The top data gets placed based on the date, which you have and it's working but the other data sets are also needing to be placed in a specific spot based on where the top data lands.

Better example below I hope. I changed the end date so Data0 got put into Place 2 but Data 0 below is still in place 1, both would need to be in place too.

That's why I thought about approaching this with this formula below but it was skipping the first IF even though the conditions were true and I couldn't figure out why, but that way if the condition was true I would know exactly where to move the data for all 3 sets of data that will be involved.

        With Sheet1
If NewBA > BA3 And NewBA > BA2 And NewBA < PBA Then
.Range("A2:B4").Copy Destination:=.Range("Q2")
.Range("C2:D2").Copy Destination:=.Range("A2")
.Range("Q2:R4").Copy Destination:=.Range("C2")
ElseIf NewBA > BA4 And NewBA > BA3 And NewBA <= BA2 And NewBA <= PBA Then
.Range("L2:M4").Copy Destination:=.Range("E2")
ElseIf NewBA > BA4 And NewBA <= BA3 And NewBA <= BA2 And NewBA <= PBA Then
.Range("L2:M4").Copy Destination:=.Range("G2")
End If
End With

TestingChrono.xlsm
	A	B	C	D	E	F	G	H	I
1	Place1		Place 2		Place 3		Place 4
2	01/29/22	02/05/22	01/22/22	01/28/22	01/15/22	01/22/22	01/01/22	01/08/22
3	data3	data3	data0	data0	data2	data2	data1	data1
4	data3	data3	data0	data0	data2	data2	data1	data1
5	data3	data3	data0	data0	data2	data2	data1	data1
6	data3	data3	data0	data0	data2	data2	data1	data1
7	data3	data3	data0	data0	data2	data2	data1	data1
8	data3	data3	data0	data0	data2	data2	data1	data1
9	data3	data3	data0	data0	data2	data2	data1	data1
10	data3	data3	data0	data0	data2	data2	data1	data1
11	data3	data3	data0	data0	data2	data2	data1	data1
12	data3	data3	data0	data0	data2	data2	data1	data1
13	data3	data3	data0	data0	data2	data2	data1	data1
14	data3	data3	data0	data0	data2	data2	data1	data1
15
16		Place 1
17	data0	data0	data0	data0	data0	data0
18	data0	data0	data0	data0	data0	data0
19	data0	data0	data0	data0	data0	data0
20	data0	data0	data0	data0	data0	data0
21	data0	data0	data0	data0	data0	data0
22	data0	data0	data0	data0	data0	data0
23	data0	data0	data0	data0	data0	data0
24	data0	data0	data0	data0	data0	data0
25		Place 2
26	data1	data1	data1	data1	data1	data1
27	data1	data1	data1	data1	data1	data1
28	data1	data1	data1	data1	data1	data1
29	data1	data1	data1	data1	data1	data1
30	data1	data1	data1	data1	data1	data1
31	data1	data1	data1	data1	data1	data1
32	data1	data1	data1	data1	data1	data1
33	data1	data1	data1	data1	data1	data1
34		Place 3
35	data2	data2	data2	data2	data2	data2
36	data2	data2	data2	data2	data2	data2
37	data2	data2	data2	data2	data2	data2
38	data2	data2	data2	data2	data2	data2
39	data2	data2	data2	data2	data2	data2
40	data2	data2	data2	data2	data2	data2
41	data2	data2	data2	data2	data2	data2
42	data2	data2	data2	data2	data2	data2
43		Place 4
44	data3	data3	data3	data3	data3	data3
45	data3	data3	data3	data3	data3	data3
46	data3	data3	data3	data3	data3	data3
47	data3	data3	data3	data3	data3	data3
48	data3	data3	data3	data3	data3	data3
49	data3	data3	data3	data3	data3	data3
50	data3	data3	data3	data3	data3	data3
51	data3	data3	data3	data3	data3	data3
52
Sheet1

[/CODE]

 

[BSALV]

so the top, which is sorting correct now (okay?), is the guideline for the other blocks to be sorted vertical, not just moved ?

 

[Glasgowsmile]

so the top, which is sorting correct now (okay?), is the guideline for the other blocks to be sorted vertical, not just moved ?

With the above example, if the sorting puts the top data in C2:D14 like above, then the below set of data for data0 should also go into the 'Place 2' or A26:F33. There would be another set of data on another worksheet, something like Data!A3:F1000 that would also need to be sorted based on where that original sort lands.