Insert multiple columns in multiple workbooks

I have about a hundred workbooks that I need to modify and I'm hoping to do that without having to open each one up individually. Each workbook only has data on one sheet.

Briefly, I need to insert four columns in between 'H' and 'I'. These columns will have fixed titles across all the workbooks.

Is it possible to do this by macro rather than opening up each workbook individually and inserting then re-naming each column? Looking for any guidance...

Thanks!

You can certainly have VBA do that for you. First 'teach' excel what you want done by recording a macro while you do it.
he wrap code around the recorded VBA to do it for the other 100 worksbooks. You can use a list of file names with paths (especially if they are in different folders), or use a loop containg DIR to find them.

I posted code here which shows how to parse folders for the files. Working from a list is much simpler.

Hi AlphaFrog, thanks for replying. I tried the code you posted, after changing the folder and headers, but the message box that popped up says '0 files processed'. Am I missing something?

Sub Insert_Columns_All_Files()
Dim strPath As String, strFile As String, Counter As Long

strPath = "S:\VOI\FCC Cases\TEST"

strFile = Dir$(strPath & "*.xls*", vbNormal)

Application.ScreenUpdating = False
Do While Len(strFile)
Counter = Counter + 1
With Workbooks.Open(strPath & strFile)
With Sheets(1)
.Columns("I:L").Insert
.Range("I1:L1").Value = Array("Control Number", "Class", "Status", "Remarks")
End With
.Close SaveChanges:=True
End With
strFile = Dir$
Loop
Application.ScreenUpdating = True

MsgBox Counter & " files proceesed. ", , "Insert Columns Complete"

End Sub

Thank you, it is now working albeit with one technical error: I saved five worksheets in the test folder and after the macro is run, the 4 columns are inserted once on the first sheet, twice on the second, three times on the third, etc.

I'm trying to figure out what value to change so it only inserts those four columns and headings once on every workbook, but I'd appreciate any input.

No they don't have headers or a code in them already. I moved five new test workbooks in the folder, but it's the same issue.

So I open the second workbook in the test folder, and on the Visual Basic for Applications page and This Workbook button, I paste the macro you provided with only the folders and headers changed. After the macro runs, I get a pop-up:

pop-up.PNG

When I click 'No', I get a Runtime Error 1004:

error.PNG

When I click OK, the spreadsheet looks like this:

headers.PNG

I just got back to work on this project. Your suggestions worked perfectly and I'm able to insert those columns across all workbooks now without further difficulty. Thanks again!