VBA Code- finding #Spill and moving the formula to the next column

[stromnoexcel]

I'm not sure if it's even possible, but I need help in the following:

so far, I made a made to insert rows, by the value of D minus 1, each time column D is >1. The macro is working from bottom to top since its trickier for the macro to function if the last row keeps changing as its going top to bottom. Columns A-D are searched by blanks and then are pasted with values below it. (Hope that makes sense)

"Sub InsertingRows_FillingBlanksWBelows()
'
' InsertingRows_FillingBlanksWBelows Macro
'
'

Dim v2 As Worksheet
Dim LastRow As Long 'Integer
Dim lngIdx As Long
Dim a As Variant
Dim J As Long

'Loop through the data range BACKWARDS, tracking each case where a row will need to be inserted
'Loop through the row numbers in the collection, which is in REVERSE order (adding rows will change the row numbers in the range,
'making forward looping hard)

Set v2 = Worksheets("v2") 'assign worksheet to variable v2

With v2
LastRow = .Cells(.Rows.Count, 4).End(xlUp).Row 'determine last row

For lngIdx = LastRow To 2 Step -1 'step thru backwards
a = .Cells(lngIdx, 4).Value
If IsNumeric(a) And a > 1 Then 'except 1
Application.ScreenUpdating = False 'turn off screen updating

'Insert new rows
For J = 1 To (a - 1)
.Rows(lngIdx).Insert Shift:=xlUp
Next J
Application.ScreenUpdating = True 'turn on screen updating
End If
Next lngIdx
End With

Range("A1:D36824").Select 'Range for D would have to get updated each refresh?
Selection.SpecialCells(xlCellTypeBlanks).Select 'finds all blanks and ='s then to one below
Application.CutCopyMode = False
Selection.FormulaR1C1 = "=R[1]C" '=copying below
'A-D is all filled in, now:

'1. FIND #Spills and move it over to the column next to it
'-allows the filter formula to spill down without being blocked
' this would mean having possibly 2 buttons. 1st one inserts the rows for caontiners>1 +fill in blanks aka macro above
' Person refreshing would then have to add the containers (alt+h+v+v) manually
' 2nd would be bring down the remaining formulas

End Sub"



Now, I'm having trouble filling the blank cells since all YouTube videos show how to fill from the row above, instead of the row below.

Any help on how to move forward is appreciated. Again my thinking is in the last paragraph comment, but if there's a way to combine everything into one macro that would be great.

TLDR: How can I get the formulas below blank cells to be switch spots and be in the blank cells? This will allow the second name to be visible and remove the #Spill. The goal of the sheet in general is to duplicate the info row in columns A-D and for row G to not have spills so the 2nd name can be seen.