Code that will put the file name in the last column for multiple workbooks

[dstar]

Big props to whoever knows how to do this. I have are around 100 different workbooks that I want to add a column at the end to that adds the file name to each row. Instead of going into each workbook and manually adding the file name to the last column does anyone know a code that can do this automatically? I've spent way too much time trying to figure this out.

Thank you!!!

 

[JLGWhiz]

If all of the workbooks are in a single directory and the workbook hosting the code is in the same directory then try this. It is assumed that only sheet 1 of each workbook applies.

Sub addFileName()
Dim wb As Workbook, lc As Long, fPath As String, fName As String
fPath = ThisWorkbook.Path
If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
fName = Dir(fPath & "*.xl*")
    Do While fName <> ""
        If fName = ThisWorkbook.Name Then GoTo SKIP:
            Set wb = Workbooks.Open(fPath & fName)
            lc = wb.Sheets(1).Cells.Find("*", , xlFormulas, xlPart, xlByColumns, xlPrevious).Column
            wb.Sheets(1).Cells(2, lc + 1).Resize(wb.Sheets(1).UsedRange.Rows.Count - 1, 1) = wb.Name
            Application.DisplayAlerts = False
                wb.Close True, fPath & fName
            Application.DisplayAlerts = True
SKIP:
            fName = Dir
    Loop
End Sub

 

[dstar]

you're a beast