How to insert "column A" into multiple workbooks automatically

Hi,

I have a set of 48 separate workbooks, each with several columns and hundreds of rows. In each of these workbooks, I want to insert a column in Column A, and in each row under the new Column A, I want to copy the name of the file. So for example, if the file is called "Sample," I want Column A to look like this:

Sample
Sample
Sample
Sample

for each row that has data in all 48 workbooks. Obviously the workbooks all have different names.

Is there a macro that could do this? Or any other way to do it?

Thanks,

George

Try this. Copy the 48 workbooks into a test folder, keeping the originals for safe keeping, in case this macro messes them up.

Put this code in a new workbook and save it in another folder, away from the 48 workbooks. Modify the code to change folder string containing the workbooks as required and run the macro.

Option Explicit

Public Sub Insert_Column_In_All_Workbooks_In_Folder()

    Dim folder As String, filename As String
    Dim destinationWorkbook As Workbook
    Dim lastRow As Long
        
    'Folder containing the 48 workbooks
    
    folder = "C:\temp\excel\"
    
    If Right(folder, 1) <> "\" Then folder = folder & "\"
    
    filename = Dir(folder & "*.xls", vbNormal)
    While Len(filename) <> 0
        'Debug.Print folder & filename
        Set destinationWorkbook = Workbooks.Open(folder & filename)
        With destinationWorkbook.Worksheets(1)
            lastRow = .Cells(.Rows.Count, "A").End(xlUp).row
            .Columns("A").Insert Shift:=xlToRight
            .Range("A1:A" & lastRow).Value = Left(filename, InStr(filename, ".") - 1)
        End With
        destinationWorkbook.Close True
        filename = Dir()  ' Get next matching file
    Wend

End Sub