Adding a column with workbook name in multiple workbooks

[fawnlemur]

Hi

I have over 500 workbooks, I want to add the workbook name in column AD for every row with text in it for all 500 spreadsheets.
I plan to merge them into one master spreadsheet but I need to know which spreadsheet the data came from.
Can someone please help me with the first part to add the workbook's name in column AD for every spreadsheet as I'm not too sure how I can do that.


I need the workbook name not the sheet name.

Thank you

 

[Fluff]

Are all the workbooks in the same folder?

 

[fawnlemur]

Are all the workbooks in the same folder?

Yes they are in the same folder

 

[Fluff]

How about

Sub fawnlemur()
   Dim Pth As String
   Dim Fname As String
   Dim Wbk As Workbook
   Dim Ws As Worksheet
   
   Application.ScreenUpdating = True
   Pth = [COLOR=#ff0000]"C:\MrExcel\Fluff\"[/COLOR]
   Fname = Dir(Pth & "*.xls*")
   Do While Fname <> ""
      Set Wbk = Workbooks.Open(Pth & Fname)
      For Each Ws In Wbk.Worksheets
         Ws.Range("AD1:AD" & Ws.Range("A" & Rows.Count).End(xlUp).Row).Value = Fname
      Next Ws
      Wbk.Close True
      Fname = Dir()
   Loop
End Sub

This uses column A to decide the last row, but than can easily be changed if needed.
Change path in red to suit

 

[fawnlemur]

Thanks this is perfect

 

[Fluff]

You're welcome & thanks for the feedback