VBA code to open workbooks in a folder, create new sheet, paste headers and formulas?

[Commoner]

Is there a way to use VBA in a master workbook that opens all workbooks in a folder then creates a new worksheet then pastes 10 headers in the new worksheet then pastes a formula below each header then closes the workbooks? I hoping the VBA code can do each workbook one at a time.

Thanks

[jolivanes]

Change references where required.

Sub Add_Sheet_To_All_Files()
    Dim sPath As String
    Dim Wb As Workbook
    Dim sFile As String
    Dim hdrArr
    hdrArr = Array("Col 1", "Col 2", "Col 3", "Col 4", "Col 5", "Col 6", "Col 7", "Col 8", "Col 9", "Col 10")
    sPath = "C:\Folder A\Sub Folder A\"
    sFile = Dir(sPath & "*.xl*")
    Application.ScreenUpdating = False
    Do While sFile <> ""
        Set Wb = Workbooks.Open(sPath & sFile)
            Wb.Sheets.Add(After:=Wb.Sheets(Sheets.Count)).Name = "Brand_New_Sheet"
                With ActiveSheet.Cells(1, 1).Resize(, 10)
                    .Value = hdrArr
                    .Offset(1).Formula = "=R[4]C+R[4]C[3]"
                End With
        Wb.Close SaveChanges:=True
        sFile = Dir
    Loop
   Application.ScreenUpdating = True
End Sub

[snb]

@joli

Create a fille with headernames and formulae and save it as G:\OF\voorbeeld.xlsx.

Now you can open each file in the directory and add the sheet with

Sheets.add,sheets(sheets.count),,"G:\OF\voorbeeld.xlsx"

[jolivanes]

Hoi snb
Thanks. I'll have to try that. Never too old to learn.

[snb]

@Joli

Never too young to teach.