Working with a combined workbook macro and need to add filename from each source book to C

Excel Ver. 2010

I have built a WB that combines the data from 50 or so WBs with a list of the files in Tab "List" and the data from each WB into Tab "Data"

I need to import the file name and write it into Column A of the Data tab. I know I will need to move the copy to column 2 and can do that. My problem is that the only code I see is how to fill data that is already in the source sheet such in the code below. My file name is not in the data sheets I am combining, so the code below does not work for that.

I am using a list of the files from the Tab "List"

Code from Live Lessons: Excel VBA and Macros with Mr Excel:

 ' Replicate the department from A2 to column A in WSD
 WSD.Cells(NextRow, 1).Resize(RowCount, 1).Value = WSN.Range("A2")

 Sub CombineFiles()
 Dim WBO As Workbook ' original workbook
 Dim WBN As Workbook ' individual data workbooks
 Dim WSL As Worksheet ' List of files worksheet
 Dim WSD As Worksheet ' data collection worksheet
 Dim WSN As Worksheet

 ' Define object variables
 Set WBO = ThisWorkbook
 Set WSL = WBO.Worksheets("List")
 Set WSD = WBO.Worksheets("Data")

 Application.ScreenUpdating = False

 ' Clear out any previous data on WSD, but leave the headings
 WSD.Cells(2, 1).Resize(Rows.Count - 1, Columns.Count).Clear

 NextRow = 2

 ' Loop through all the files on WSL
 FinalRow = WSL.Cells(Rows.Count, 1).End(xlUp).Row
 For i = 1 To FinalRow
 ThisFile = WSL.Cells(i, 1)
 ' Open a file
 Set WBN = Workbooks.Open(Filename:=ThisFile)

 For Each WSN In WBN.Worksheets

 ' Last row in this file? RowCount is 4 less <- Not Used Here use -1 to not grab headers.
 LastRow = WSN.Cells(Rows.Count, 1).End(xlUp).Row
 RowCount = LastRow - 1

 ' Copy from 5 to last row over to column B
 WSN.Cells(2, 1).Resize(RowCount, 20).Copy Destination:=WSD.Cells(NextRow, 1)

 -> ' Replicate the department from A2 to column A in WSD
 -> ' WSD.Cells(NextRow, 1).Resize(RowCount, 1).Value = WSN.Range("A2")

 ' Set up the new NextRow
 NextRow = NextRow + RowCount
 Next WSN

 ' Close WSN, don't save
 WBN.Close SaveChanges:=False

 Next i

 MsgBox "FILES HAVE BEEN PROCESSED"
 Application.ScreenUpdating = True
 End Sub

Thanks!
-SS

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(Because you are new to the forum, I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)