VBA Loop to clear contents of fiscal year rows in specific month columns

[agonyrose]

I've been working on a VBA macro to clear the contents of certain month columns if they are in the current fiscal year or past fiscal year. I've gotten the variables and fiscal years to calculate correctly based on the July to June fiscal year, I just can't seem to figure out the correct structure of For Each or For If statement to loop through the Accounting Year (Column A) and clear the specific months rows in columns Q through AB.

Example scenario: Current Date is October 15 2022 (FY2023(Because after July)) I need to clear the current month of October (in Fiscal year 2023) and all months prior to October in Fiscal year 2022 to have 12 months of complete data.

Let me know if this is dumb and I should just continue doing this manually by sorting rows and clearing out the month columns. I've been staring at the code and googling VBA for loops(If and ForEach, and .clearcontents) for a week now and finally my stubbornness has resorted to asking for help.

Here is my VBA code so far. All of the code works but I have no idea how to structure the bottom two ForEach statements.

Sub ExtractedCleanDatatest()
   
    'steps insert the formulas needed, rename the month usage to monthname'
    'run a loop to delete current month of currentFT and all months prior to current month past FY'
    'variables needed range of months, currentfy, past fy, currentmo, loop range,
   
    'dimensions (variables)'
    Dim Mo As Range
    Dim FY As Range
    Dim Pyx As Range
    Dim CurrentFY As Integer
    Dim PastFY As Integer
    Dim CurrentMonth As String
    Dim CurMo As Integer
    Dim ClearMo As Integer
   
    'constants'
    CurrentFY = IIf(Month(Date) <= 6, Year(Date), Year(Date) + 1)
    PastFY = IIf(Month(Date) <= 6, Year(Date), Year(Date))
    CurrentMonth = MonthName(Month(Now))
    CurMo = Month(Now)
    ClearMo = CurMo - 1 - 6
   
    'Calculation functions inserts columns from N-P labels columns inserts functions into N2--P2 fills down the functions'
   
    Range("N:P").EntireColumn.Insert
    Range("N1").Formula = "Lowest UOM Price"
    Range("O1").Formula = "12 Mo Use"
    Range("P1").Formula = "12 Mo Spend"
    Range("N2").Formula = "=L2/J2"
    Range("O2").Formula = "=SUM(Q2:AB2)"
    Range("P2").Formula = "=O2*N2"
    Range("N2:N" & Cells(Rows.Count, 12).End(xlUp).Row).FillDown
    Range("O2:O" & Cells(Rows.Count, 12).End(xlUp).Row).FillDown
    Range("P2:P" & Cells(Rows.Count, 12).End(xlUp).Row).FillDown
   
    'rename months Q-AB from July to June (fy order)
   
    Range("Q1").Formula = "July"
    Range("R1").Formula = "August"
    Range("S1").Formula = "September"
    Range("T1").Formula = "October"
    Range("U1").Formula = "November"
    Range("V1").Formula = "December"
    Range("W1").Formula = "January"
    Range("X1").Formula = "February"
    Range("Y1").Formula = "March"
    Range("Z1").Formula = "April"
    Range("AA1").Formula = "May"
    Range("AB1").Formula = "June"
   
    'loop to delete pyx rows in column c'
    Dim i As Integer
    For i = 5000 To 2 Step -1
        If Cells(i, 3).Value Like "*PYX*" Then
            Cells(i, 3).EntireRow.Delete
        End If
    Next i
   
    'clear contents of month columns based on FY row and current month'
    'For Each Cell In "A:A"
        'If Cell.Value = CurrentFY Then
            'clear row of current mo
    'Next
    'For Each Cell in "A:A"
        'If Cell.value = PastFY Then
            'Clear rows of Current month minus 1 to July
   
End Sub

Below is an example of my spreadsheet (headers are actual, but the othe data is sanitized)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	Accounting Year	Issue To	Usage Charge Dept	Usage Charge Dept Desc	Expense Code	Item No	Primary Vendor Mfr	Item Desc	MFR Item No	UOM Dft Purchase Conv Factor	UOM Dft Purchase	UOM Dft Purchase Price	UOM Lowest	Lowest UOM Price	12 Mo Use	12 Mo Spend	JUL Usg Qty	AUG Usg Qty	SEP Usg Qty	OCT Usg Qty	NOV Usg Qty	DEC Usg Qty	JAN Usg Qty	FEB Usg Qty	MAR Usg Qty	APR Usg Qty	MAY Usg Qty	JUN Usg Qty
2	2021	20	6531	deptabc	SCxxxx	123456	vendorabc	itemabc	1234	10	BX	12	EA	1.2	40	48	0	0	0	0	0	0	0	0	0	0	40	0
3	2022	20	6532	deptabc	SCxxxx	123457	vendorabc	itemabc	1235	11	BX	13	EA	1.18	19	22.5	0	19	0	0	0	0	0	0	0	0	0	0
4	2021	20	6533	deptabc	SCxxxx	123458	vendorabc	itemabc	1236	12	EA	14	EA	1.17	24	28	0	0	1	0	3	0	0	0	0	19	0	1
5	2022	20	6534	deptabc	SCxxxx	123459	vendorabc	itemabc	1237	13	EA	15	EA	1.15	15	17.3	0	0	15	0	0	0	0	0	0	0	0	0
6	2021	20	6535	deptabc	SCxxxx	123460	vendorabc	itemabc	1238	14	EA	16	EA	1.14	19	21.7	0	0	0	0	0	0	0	0	0	10	9	0
7	2021	20	6536	deptabc	SCxxxx	123461	vendorabc	itemabc	1239	15	EA	17	EA	1.13	26	29.5	0	0	0	3	13	0	0	0	0	10	0	0
8	2021	20	6537	deptabc	SCxxxx	123462	vendorabc	itemabc	1240	16	EA	18	EA	1.13	1	1.13	0	0	1	0	0	0	0	0	0	0	0	0
9	2021	20	6538	deptabc	SCxxxx	123463	vendorabc	itemabc	1241	17	EA	19	EA	1.12	2	2.24	1	0	0	0	0	0	0	0	0	0	0	1
10	2021	20	6539	deptabc	SCxxxx	123464	vendorabc	itemabc	1242	18	BX	20	EA	1.11	-50	-56	0	-50	0	0	0	0	0	0	0	0	0	0
11	2022	20	6540	deptabc	SCxxxx	123465	vendorabc	itemabc	1243	19	EA	21	EA	1.11	1	1.11	1	0	0	0	0	0	0	0	0	0	0	0
12	2021	20	6541	deptabc	SCxxxx	123466	vendorabc	itemabc	1244	20	EA	22	EA	1.1	0	0	0	0	0	0	0	0	0	0	0	0	0	0
13	2021	20	6542	deptabc	SCxxxx	123467	vendorabc	itemabc	1245	21	EA	23	EA	1.1	1	1.1	0	0	0	0	0	0	0	0	1	0	0	0
14	2021	20	6543	deptabc	SCxxxx	123468	vendorabc	itemabc	1246	22	EA	24	EA	1.09	1	1.09	1	0	0	0	0	0	0	0	0	0	0	0
15	2021	20	6544	deptabc	SCxxxx	123469	vendorabc	itemabc	1247	23	EA	25	EA	1.09	2	2.17	0	0	0	0	0	0	0	0	2	0	0	0
16	2021	20	6545	deptabc	SCxxxx	123470	vendorabc	itemabc	1248	24	EA	26	EA	1.08	1	1.08	1	0	0	0	0	0	0	0	0	0	0	0
17	2021	20	6546	deptabc	SCxxxx	123471	vendorabc	itemabc	1249	25	BX	27	EA	1.08	20	21.6	0	0	0	0	20	0	0	0	0	0	0	0
18	2021	20	6547	deptabc	SCxxxx	123472	vendorabc	itemabc	1250	26	BX	28	EA	1.08	20	21.5	0	0	0	20	0	0	0	0	0	0	0	0
19	2021	20	6548	deptabc	SCxxxx	123473	vendorabc	itemabc	1251	27	EA	29	EA	1.07	10	10.7	0	10	0	0	0	0	0	0	0	0	0	0
20	2021	20	6549	deptabc	SCxxxx	123474	vendorabc	itemabc	1252	28	EA	30	EA	1.07	2	2.14	0	2	0	0	0	0	0	0	0	0	0	0
21	2021	20	6550	deptabc	SCxxxx	123475	vendorabc	itemabc	1253	29	EA	31	EA	1.07	16	17.1	0	5	0	0	3	0	0	4	0	4	0	0
22	2022	20	6551	deptabc	SCxxxx	123476	vendorabc	itemabc	1254	30	EA	32	EA	1.07	4	4.27	0	4	0	0	0	0	0	0	0	0	0	0
23	2021	20	6552	deptabc	SCxxxx	123477	vendorabc	itemabc	1255	31	EA	33	EA	1.06	34	36.2	34	0	0	0	0	0	0	0	0	0	0	0
24	2022	20	6553	deptabc	SCxxxx	123478	vendorabc	itemabc	1256	32	CA	34	EA	1.06	10	10.6	10	0	0	0	0	0	0	0	0	0	0	0
25	2022	20	6554	deptabc	SCxxxx	123479	vendorabc	itemabc	1257	33	PK	35	EA	1.06	10	10.6	0	10	0	0	0	0	0	0	0	0	0	0
26	2021	20	6555	deptabc	SCxxxx	123480	vendorabc	itemabc	1258	34	CA	36	EA	1.06	100	106	0	0	0	0	0	50	50	0	0	0	0	0
27	2021	20	6556	deptabc	SCxxxx	123481	vendorabc	itemabc	1259	35	CA	37	EA	1.06	100	106	0	0	0	0	0	0	0	0	0	100	0	0
28	2021	20	6557	deptabc	SCxxxx	123482	vendorabc	itemabc	1260	36	EA	38	EA	1.06	587	620	0	0	0	0	0	337	0	0	0	200	50	0
29	2021	20	6558	deptabc	SCxxxx	123483	vendorabc	itemabc	1261	37	EA	39	EA	1.05	3037	3201	254	240	195	344	198	242	220	282	245	241	273	303
30	2021	20	6559	deptabc	SCxxxx	123484	vendorabc	itemabc	1262	38	EA	40	EA	1.05	2566	2701	244	252	219	323	122	100	164	287	167	35	308	345
31	2022	20	6560	deptabc	SCxxxx	123485	vendorabc	itemabc	1263	39	EA	41	EA	1.05	727	764	320	296	111	0	0	0	0	0	0	0	0	0
32	2022	20	6561	deptabc	SCxxxx	123486	vendorabc	itemabc	1264	40	EA	42	EA	1.05	720	756	328	296	96	0	0	0	0	0	0	0	0	0
33	2021	20	6562	deptabc	SCxxxx	123487	vendorabc	itemabc	1265	41	PK	43	EA	1.05	-10	-10	0	0	0	0	0	0	-10	0	0	0	0	0
34	2021	20	6563	deptabc	SCxxxx	123488	vendorabc	itemabc	1266	42	PK	44	EA	1.05	20	21	0	0	0	0	0	10	0	0	10	0	0	0
35	2021	20	6564	deptabc	SCxxxx	123489	vendorabc	itemabc	1267	43	PK	45	EA	1.05	2	2.09	0	0	0	0	0	2	0	0	0	0	0	0
36	2021	20	6565	deptabc	SCxxxx	123490	vendorabc	itemabc	1268	44	CA	46	EA	1.05	100	105	0	0	0	0	20	10	20	10	0	40	0	0
37	2021	20	6566	deptabc	SCxxxx	123491	vendorabc	itemabc	1269	45	CA	47	EA	1.04	-20	-21	0	0	0	0	0	0	-20	0	0	0	0	0
38	2021	20	6567	deptabc	SCxxxx	123492	vendorabc	itemabc	1270	46	EA	48	EA	1.04	229	239	0	0	0	0	0	21	17	37	76	58	18	2
39	2021	20	6568	deptabc	SCxxxx	123493	vendorabc	itemabc	1271	47	EA	49	EA	1.04	229	239	0	0	0	0	20	0	48	41	68	32	20	0
40	2021	20	6569	deptabc	SCxxxx	123494	vendorabc	itemabc	1272	48	EA	50	EA	1.04	1	1.04	0	0	0	0	0	0	0	0	0	0	0	1
41	2021	20	6570	deptabc	SCxxxx	123495	vendorabc	itemabc	1273	49	EA	51	EA	1.04	0	0	0	0	-10	10	0	0	0	0	0	0	0	0
42	2021	20	6571	deptabc	SCxxxx	123496	vendorabc	itemabc	1274	50	EA	52	EA	1.04	20	20.8	0	20	0	0	0	0	0	0	0	0	0	0
43	2021	20	6572	deptabc	SCxxxx	123497	vendorabc	itemabc	1275	51	EA	53	EA	1.04	17	17.7	0	4	0	0	1	0	0	0	3	9	0	0
44	2021	20	6573	deptabc	SCxxxx	123498	vendorabc	itemabc	1276	52	EA	54	EA	1.04	35	36.3	0	6	0	0	6	0	0	10	8	3	1	1
45	2022	20	6574	deptabc	SCxxxx	123499	vendorabc	itemabc	1277	53	EA	55	EA	1.04	4	4.15	2	2	0	0	0	0	0	0	0	0	0	0
46	2021	20	6575	deptabc	SCxxxx	123500	vendorabc	itemabc	1278	54	EA	56	EA	1.04	4	4.15	0	3	0	0	0	0	0	0	0	0	0	1
47	2022	20	6576	deptabc	SCxxxx	123501	vendorabc	itemabc	1279	55	EA	57	EA	1.04	1	1.04	1	0	0	0	0	0	0	0	0	0	0	0
48	2021	20	6577	deptabc	SCxxxx	123502	vendorabc	itemabc	1280	56	EA	58	EA	1.04	1	1.04	0	0	1	0	0	0	0	0	0	0	0	0
49	2022	20	6578	deptabc	SCxxxx	123503	vendorabc	itemabc	1281	57	EA	59	EA	1.04	1	1.04	0	1	0	0	0	0	0	0	0	0	0	0
50	2021	20	6579	deptabc	SCxxxx	123504	vendorabc	itemabc	1282	58	BX	60	EA	1.03	20	20.7	0	0	0	0	0	0	0	0	0	0	20	0
Extracted

Cell Formulas
Range	Formula
N2:N50	N2	=L2/J2
O2:O50	O2	=SUM(Q2:AB2)
P2:P50	P2	=O2*N2

 

[kevin9999]

I need to clear the current month of October (in Fiscal year 2023) and all months prior to October in Fiscal year 2022 to have 12 months of complete data.

This is the bit I'm not following. If you cleared October (current FYTD) and all previous financial years' data, where does the 12 months of complete data come from? Could you define what those 12 months would be given your example scenario?

 

[bebo021999]

Try to add another sheet, sheet2, with your expected outcome from sheet1

 

[agonyrose]

This is the bit I'm not following. If you cleared October (current FYTD) and all previous financial years' data, where does the 12 months of complete data come from? Could you define what those 12 months would be given your example scenario?

It would be the prior 12 months to the current month of the current fiscal year. So if I pulled this report in october calendar year 2022, that would be FY2023, So I would clear the current month since it isn't a completed month of data and use the data from October FY22 to Sep FY23. I may have explained that strangely in the original post.

Try to add another sheet, sheet2, with your expected outcome from sheet1

Here is the expected outcome. Essentially what I do manually is sort column A to a single fiscal year, then clear the current month of the current fiscal year, then clear the months prior to the current month in the past fiscal year. That's kind of what I was using the "ClearMo" dimension for because it gives the amount of months back to clear for the past fiscal year.

vba code test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	Accounting Year	Issue To	Usage Charge Dept	Usage Charge Dept Desc	Expense Code	Item No	Primary Vendor Mfr	Item Desc	MFR Item No	UOM Dft Purchase Conv Factor	UOM Dft Purchase	UOM Dft Purchase Price	UOM Lowest	Lowest UOM Price	12 Mo Use	12 Mo Spend	JUL Usg Qty	AUG Usg Qty	SEP Usg Qty	OCT Usg Qty	NOV Usg Qty	DEC Usg Qty	JAN Usg Qty	FEB Usg Qty	MAR Usg Qty	APR Usg Qty	MAY Usg Qty	JUN Usg Qty
2	2021	20	6531	deptabc	SCxxxx	123456	vendorabc	itemabc	1234	10	BX	12	EA	1.2	40	48	0	0	0	0	0	0	0	0	0	0	40	0
3	2022	20	6532	deptabc	SCxxxx	123457	vendorabc	itemabc	1235	11	BX	13	EA	1.1818182	19	22.454545	0	19	0	0	0	0	0	0	0	0	0	0
4	2021	20	6533	deptabc	SCxxxx	123458	vendorabc	itemabc	1236	12	EA	14	EA	1.1666667	23	26.833333	0	0	0	0	3	0	0	0	0	19	0	1
5	2022	20	6534	deptabc	SCxxxx	123459	vendorabc	itemabc	1237	13	EA	15	EA	1.1538462	15	17.307692	0	0	15	0	0	0	0	0	0	0	0	0
6	2021	20	6535	deptabc	SCxxxx	123460	vendorabc	itemabc	1238	14	EA	16	EA	1.1428571	19	21.714286	0	0	0	0	0	0	0	0	0	10	9	0
7	2021	20	6536	deptabc	SCxxxx	123461	vendorabc	itemabc	1239	15	EA	17	EA	1.1333333	26	29.466667	0	0	0	3	13	0	0	0	0	10	0	0
8	2021	20	6537	deptabc	SCxxxx	123462	vendorabc	itemabc	1240	16	EA	18	EA	1.125	0	0	0	0	0	0	0	0	0	0	0	0	0	0
9	2021	20	6538	deptabc	SCxxxx	123463	vendorabc	itemabc	1241	17	EA	19	EA	1.1176471	1	1.1176471	0	0	0	0	0	0	0	0	0	0	0	1
10	2021	20	6539	deptabc	SCxxxx	123464	vendorabc	itemabc	1242	18	BX	20	EA	1.1111111	0	0	0	0	0	0	0	0	0	0	0	0	0	0
11	2022	20	6540	deptabc	SCxxxx	123465	vendorabc	itemabc	1243	19	EA	21	EA	1.1052632	1	1.1052632	1	0	0	0	0	0	0	0	0	0	0	0
12	2021	20	6541	deptabc	SCxxxx	123466	vendorabc	itemabc	1244	20	EA	22	EA	1.1	0	0	0	0	0	0	0	0	0	0	0	0	0	0
13	2021	20	6542	deptabc	SCxxxx	123467	vendorabc	itemabc	1245	21	EA	23	EA	1.0952381	1	1.0952381	0	0	0	0	0	0	0	0	1	0	0	0
14	2021	20	6543	deptabc	SCxxxx	123468	vendorabc	itemabc	1246	22	EA	24	EA	1.0909091	0	0	0	0	0	0	0	0	0	0	0	0	0	0
15	2021	20	6544	deptabc	SCxxxx	123469	vendorabc	itemabc	1247	23	EA	25	EA	1.0869565	2	2.173913	0	0	0	0	0	0	0	0	2	0	0	0
16	2021	20	6545	deptabc	SCxxxx	123470	vendorabc	itemabc	1248	24	EA	26	EA	1.0833333	0	0	0	0	0	0	0	0	0	0	0	0	0	0
17	2021	20	6546	deptabc	SCxxxx	123471	vendorabc	itemabc	1249	25	BX	27	EA	1.08	20	21.6	0	0	0	0	20	0	0	0	0	0	0	0
18	2021	20	6547	deptabc	SCxxxx	123472	vendorabc	itemabc	1250	26	BX	28	EA	1.0769231	20	21.538462	0	0	0	20	0	0	0	0	0	0	0	0
19	2021	20	6548	deptabc	SCxxxx	123473	vendorabc	itemabc	1251	27	EA	29	EA	1.0740741	0	0	0	0	0	0	0	0	0	0	0	0	0	0
20	2021	20	6549	deptabc	SCxxxx	123474	vendorabc	itemabc	1252	28	EA	30	EA	1.0714286	0	0	0	0	0	0	0	0	0	0	0	0	0	0
21	2021	20	6550	deptabc	SCxxxx	123475	vendorabc	itemabc	1253	29	EA	31	EA	1.0689655	11	11.758621	0	0	0	0	3	0	0	4	0	4	0	0
22	2022	20	6551	deptabc	SCxxxx	123476	vendorabc	itemabc	1254	30	EA	32	EA	1.0666667	4	4.2666667	0	4	0	0	0	0	0	0	0	0	0	0
23	2021	20	6552	deptabc	SCxxxx	123477	vendorabc	itemabc	1255	31	EA	33	EA	1.0645161	0	0	0	0	0	0	0	0	0	0	0	0	0	0
24	2022	20	6553	deptabc	SCxxxx	123478	vendorabc	itemabc	1256	32	CA	34	EA	1.0625	10	10.625	10	0	0	0	0	0	0	0	0	0	0	0
25	2022	20	6554	deptabc	SCxxxx	123479	vendorabc	itemabc	1257	33	PK	35	EA	1.0606061	10	10.606061	0	10	0	0	0	0	0	0	0	0	0	0
26	2021	20	6555	deptabc	SCxxxx	123480	vendorabc	itemabc	1258	34	CA	36	EA	1.0588235	100	105.88235	0	0	0	0	0	50	50	0	0	0	0	0
27	2021	20	6556	deptabc	SCxxxx	123481	vendorabc	itemabc	1259	35	CA	37	EA	1.0571429	100	105.71429	0	0	0	0	0	0	0	0	0	100	0	0
28	2021	20	6557	deptabc	SCxxxx	123482	vendorabc	itemabc	1260	36	EA	38	EA	1.0555556	587	619.61111	0	0	0	0	0	337	0	0	0	200	50	0
29	2021	20	6558	deptabc	SCxxxx	123483	vendorabc	itemabc	1261	37	EA	39	EA	1.0540541	2348	2474.9189	0	0	0	344	198	242	220	282	245	241	273	303
30	2021	20	6559	deptabc	SCxxxx	123484	vendorabc	itemabc	1262	38	EA	40	EA	1.0526316	1851	1948.4211	0	0	0	323	122	100	164	287	167	35	308	345
31	2022	20	6560	deptabc	SCxxxx	123485	vendorabc	itemabc	1263	39	EA	41	EA	1.0512821	727	764.28205	320	296	111	0	0	0	0	0	0	0	0	0
32	2022	20	6561	deptabc	SCxxxx	123486	vendorabc	itemabc	1264	40	EA	42	EA	1.05	720	756	328	296	96	0	0	0	0	0	0	0	0	0
33	2021	20	6562	deptabc	SCxxxx	123487	vendorabc	itemabc	1265	41	PK	43	EA	1.0487805	-10	-10.4878	0	0	0	0	0	0	-10	0	0	0	0	0
34	2021	20	6563	deptabc	SCxxxx	123488	vendorabc	itemabc	1266	42	PK	44	EA	1.047619	20	20.952381	0	0	0	0	0	10	0	0	10	0	0	0
35	2021	20	6564	deptabc	SCxxxx	123489	vendorabc	itemabc	1267	43	PK	45	EA	1.0465116	2	2.0930233	0	0	0	0	0	2	0	0	0	0	0	0
36	2021	20	6565	deptabc	SCxxxx	123490	vendorabc	itemabc	1268	44	CA	46	EA	1.0454545	100	104.54545	0	0	0	0	20	10	20	10	0	40	0	0
37	2021	20	6566	deptabc	SCxxxx	123491	vendorabc	itemabc	1269	45	CA	47	EA	1.0444444	-20	-20.88889	0	0	0	0	0	0	-20	0	0	0	0	0
38	2021	20	6567	deptabc	SCxxxx	123492	vendorabc	itemabc	1270	46	EA	48	EA	1.0434783	229	238.95652	0	0	0	0	0	21	17	37	76	58	18	2
39	2021	20	6568	deptabc	SCxxxx	123493	vendorabc	itemabc	1271	47	EA	49	EA	1.0425532	229	238.74468	0	0	0	0	20	0	48	41	68	32	20	0
40	2021	20	6569	deptabc	SCxxxx	123494	vendorabc	itemabc	1272	48	EA	50	EA	1.0416667	1	1.0416667	0	0	0	0	0	0	0	0	0	0	0	1
41	2021	20	6570	deptabc	SCxxxx	123495	vendorabc	itemabc	1273	49	EA	51	EA	1.0408163	10	10.408163	0	0	0	10	0	0	0	0	0	0	0	0
42	2021	20	6571	deptabc	SCxxxx	123496	vendorabc	itemabc	1274	50	EA	52	EA	1.04	0	0	0	0	0	0	0	0	0	0	0	0	0	0
43	2021	20	6572	deptabc	SCxxxx	123497	vendorabc	itemabc	1275	51	EA	53	EA	1.0392157	13	13.509804	0	0	0	0	1	0	0	0	3	9	0	0
44	2021	20	6573	deptabc	SCxxxx	123498	vendorabc	itemabc	1276	52	EA	54	EA	1.0384615	29	30.115385	0	0	0	0	6	0	0	10	8	3	1	1
45	2022	20	6574	deptabc	SCxxxx	123499	vendorabc	itemabc	1277	53	EA	55	EA	1.0377358	4	4.1509434	2	2	0	0	0	0	0	0	0	0	0	0
46	2021	20	6575	deptabc	SCxxxx	123500	vendorabc	itemabc	1278	54	EA	56	EA	1.037037	1	1.037037	0	0	0	0	0	0	0	0	0	0	0	1
47	2022	20	6576	deptabc	SCxxxx	123501	vendorabc	itemabc	1279	55	EA	57	EA	1.0363636	1	1.0363636	1	0	0	0	0	0	0	0	0	0	0	0
48	2021	20	6577	deptabc	SCxxxx	123502	vendorabc	itemabc	1280	56	EA	58	EA	1.0357143	0	0	0	0	0	0	0	0	0	0	0	0	0	0
49	2022	20	6578	deptabc	SCxxxx	123503	vendorabc	itemabc	1281	57	EA	59	EA	1.0350877	1	1.0350877	0	1	0	0	0	0	0	0	0	0	0	0
50	2021	20	6579	deptabc	SCxxxx	123504	vendorabc	itemabc	1282	58	BX	60	EA	1.0344828	20	20.689655	0	0	0	0	0	0	0	0	0	0	20	0
Sheet1

Cell Formulas
Range	Formula
N2:N50	N2	=L2/J2
O2:O50	O2	=SUM(Q2:AB2)
P2:P50	P2	=O2*N2

 

[kevin9999]

Here is the expected outcome.

I'm still not quite following it. For example, in your expected outcome, I see figures for April-2021. How is that part of the 12 months from September 2022 (last month of the current financial year to be included as per your clarification) going back 12 months?

 

[kevin9999]

OK, give this a try on a copy of your data. I think it's what you're looking for.

Option Explicit
Sub agonyrose()

    Dim EndDate As Date, StartDate As Date
    Dim CellDate As Long, FirstDate As Long, LastDate As Long, LRow As Long
    Dim DateString As String
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")   '<<< change to actual sheet name
    
    'Do the formula stuff
    LRow = ws.Cells.Find("*", , xlFormulas, , 1, 2).Row
    ws.Range("N:P").EntireColumn.Insert
    ws.Range("N1").Resize(, 3).Value = Array("Lowest UOM Price", "12 Mo Use", "12 Mo Spend")
    With ws.Range("N2:N" & LRow)
        .Formula = "=L2/J2"
        .Offset(, 1).Formula = "=SUM(Q2:AB2)"
        .Offset(, 2).Formula = "=O2*N2"
    End With
    
    'Change the month headers
    ws.Range("Q1").Resize(, 12).Value = Array("July", "August", "September", "October", _
    "November", "December", "January", "February", "March", "April", "May", "June")
    
    'Clear data not fitting into the required date range
    EndDate = WorksheetFunction.EoMonth(Now(), -1)
    LastDate = DateValue(EndDate)
    StartDate = WorksheetFunction.EoMonth(Now(), -13) + 1
    FirstDate = DateValue(StartDate)
    
    Dim ArrIn, ArrOut, i As Long, j As Long
    ArrIn = ws.Range("Q2:AB" & LRow)
    ArrOut = ws.Range("Q2:AB" & LRow)
    
    For i = 1 To UBound(ArrIn, 1)
        For j = 1 To UBound(ArrIn, 2)
            DateString = "1/" & Month(DateValue("1 " & ws.Cells(1, j + 16) & " 2000")) & "/" & ws.Cells(i + 1, 1).Value
            CellDate = DateValue(DateString)
                If CellDate >= FirstDate And CellDate <= LastDate Then
                    ArrOut(i, j) = ArrIn(i, j)
                Else
                    ArrOut(i, j) = vbNullString
                End If
        Next j
    Next i
    
    ws.Range("Q2").Resize(UBound(ArrOut, 1), UBound(ArrOut, 2)).Value = ArrOut

End Sub

 

[kevin9999]

EDITED (added the delete "PYX" values from column C)

Option Explicit
Sub agonyrose_2()

    Dim EndDate As Date, StartDate As Date
    Dim CellDate As Long, FirstDate As Long, LastDate As Long, LRow As Long
    Dim DateString As String
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")   '<<< change to actual sheet name
    
    'Do the formula stuff
    LRow = ws.Cells.Find("*", , xlFormulas, , 1, 2).Row
    ws.Range("N:P").EntireColumn.Insert
    ws.Range("N1").Resize(, 3).Value = Array("Lowest UOM Price", "12 Mo Use", "12 Mo Spend")
    With ws.Range("N2:N" & LRow)
        .Formula = "=L2/J2"
        .Offset(, 1).Formula = "=SUM(Q2:AB2)"
        .Offset(, 2).Formula = "=O2*N2"
    End With
    
    'Change the month headers
    ws.Range("Q1").Resize(, 12).Value = Array("July", "August", "September", "October", _
    "November", "December", "January", "February", "March", "April", "May", "June")
    
    'Get rid of the pyx rows in column C
    With ws.Range("A1").CurrentRegion
        .AutoFilter 3, "*PYX*"
        If Range("A:A").SpecialCells(xlCellTypeVisible).Rows.Count > 1 Then
            .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
        End If
        .AutoFilter
    End With
    
    'Clear data not fitting into the required date range
    EndDate = WorksheetFunction.EoMonth(Now(), -1)
    LastDate = DateValue(EndDate)
    StartDate = WorksheetFunction.EoMonth(Now(), -13) + 1
    FirstDate = DateValue(StartDate)
    
    Dim ArrIn, ArrOut, i As Long, j As Long
    ArrIn = ws.Range("Q2:AB" & LRow)
    ArrOut = ws.Range("Q2:AB" & LRow)
    
    For i = 1 To UBound(ArrIn, 1)
        For j = 1 To UBound(ArrIn, 2)
            DateString = "1/" & Month(DateValue("1 " & ws.Cells(1, j + 16) & " 2000")) & "/" & ws.Cells(i + 1, 1).Value
            CellDate = DateValue(DateString)
                If CellDate >= FirstDate And CellDate <= LastDate Then
                    ArrOut(i, j) = ArrIn(i, j)
                Else
                    ArrOut(i, j) = vbNullString
                End If
        Next j
    Next i
    
    ws.Range("Q2").Resize(UBound(ArrOut, 1), UBound(ArrOut, 2)).Value = ArrOut

End Sub