Trending Sum Macro

[pavanranjan]

Hi Team,
Need help to create a macro for 12 month sliding to right for Sum their values

Yellow marked will be 12 months in a row which is available data
Blue marked in the desired output post inserting three columns post city column. Grand total formula is also required

Tending DSO

Tending DSO

Tending DSO

202103

202103

202102

202102

202102

202101

202101

202101

202100

202100

202100

Customer no

Customer Name

City

Sales

AR

DSO

Sales

AR

DSO

Sales

AR

DSO

Sales

AR

DSO

Sales

AR

DSO

00123456

North Cansus

NYC

400

360

27

100​

90​

27​

100​

90​

27​

100​

90​

27​

100​

90​

27​

00123457

Mount Cenai

ABC

1000

900

27

250​

225​

27​

250​

225​

27​

250​

225​

27​

250​

225​

27​

00123459

Holy child

ZBA

1600

1440

27

400​

360​

27​

400​

360​

27​

400​

360​

27​

400​

360​

27​

Total

3000

2700

27

750

675

27

750

675

27

750

675

27

750

675

27

 

[offthelip]

try this code:

Sub test()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
' load all the data to column R
inarr = Range(Cells(1, 1), Cells(lastrow, 18))
For i = 1 To 18
 If inarr(2, i) = "Sales" Or inarr(2, i) = "AR" Then
    tot = 0
   For j = 3 To lastrow
    tot = tot + inarr(j, i)
   Next j
   Range(Cells(lastrow + 1, i), Cells(lastrow + 1, i)) = tot
 End If
If inarr(2, i) = "DSO" Then
 Range(Cells(lastrow + 1, i), Cells(lastrow + 1, i)) = inarr(lastrow, i)
End If
Next i

End Sub