Inserting new column across multiple spreadsheets and filling them

Hi everyone,
So I have been tasked with inserting a new column across multiple (around 300 ) spreadsheets and filling that column with data. I have minimal experience with excel and programming but doing this manually would be a nightmare. Is there a way to make excel automatically do this?

The spreadsheets have a varying number of rows, but the column will always need to be inserted at E. Also, E1 should be filled with "New Name" and every row below that with the formula: "=SUBSTITUTE(D2,MID(D2,LEN(D2)-9,6),"20"&RIGHT(MID(D2,LEN(D2)-9,6),2)&LEFT(MID(D2,LEN(D2)-9,6),2)&MID(MID(D2,LEN(D2)-9,6),3,2))"

Let me know if there's any questions. Any help y'all could give me would be greatly appreciated. Thanks in advance!

Does this help?

Sub rsmith10()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
    Columns(5).Insert
    ws.Cells(1, 5) = "New Name"
    ws.Range(Cells(2, 5), Cells(ws.Range("F" & Rows.count).End(3)(1).Row, 5)).Formula = "=SUBSTITUTE(D2,MID(D2,LEN(D2)-9,6),""20""&RIGHT(MID(D2,LEN(D2)-9,6),2)&LEFT(MID(D2,LEN(D2)-9,6),2)&MID(MID(D2,LEN(D2)-9,6),3,2))"
Next ws
End Sub