I found a vba script online that would gather data from multiple worksheets located in one directory/folder and have the same number of columns and layout as my master spreadsheet hosting the vba code and all the data. The issue is that I would like the code to also add a column with the name of the file (workbook) the data in that line was extracted from.
Can anyone please have a look at the code and send me a revised code with the above addition?
Thank you very much!!!
I posted this in another forum too: http://www.excelforum.com/exce…orkbooks.html#post3933167
Below is the code I currently have:
Option Explicit Sub cons_data() Dim Master As Workbook Dim sourceBook As Workbook Dim sourceData As Worksheet Dim CurrentFileName As String Dim myPath As String Dim lastrow As Long Dim lrow As Long Dim i As Long Dim sFil As String Application.ScreenUpdating = False Application.DisplayAlerts = False 'The folder containing the files to be recap'd myPath = "C:\Users\testing\" 'Finds the name of the first file of type .xls in the current directory CurrentFileName = Dir(myPath & "\*.xlsx") 'Create a workbook for the recap report Set Master = ThisWorkbook For i = 1 To Master.Worksheets.Count With Master.Worksheets(i) lrow = .Range("A" & .Rows.Count).End(xlUp).Row If lrow > 1 Then .Rows("2:" & lrow).ClearContents End With Next i Do Workbooks.Open (myPath & "\" & CurrentFileName) Set sourceBook = Workbooks(CurrentFileName) For i = 1 To sourceBook.Worksheets.Count Set sourceData = sourceBook.Worksheets(i) With sourceData lastrow = Master.Worksheets(.Name).Range("A" & Rows.Count).End(xlUp).Row lrow = .Range("A" & .Rows.Count).End(xlUp).Row .Rows("2:" & lrow).Copy Master.Worksheets(.Name).Rows(lastrow + 1) End With Next i sourceBook.Close 'Calling DIR w/o argument finds the next .xlsx file within the current directory. CurrentFileName = Dir() Loop While CurrentFileName <> "" MsgBox "Done" Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub