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.