I have a code which merges all excel files data in a single file. Now i want to add the file name as a column to each row or to a first row in each file's data.
what should the commands added to to it, please help. More so please help in making the file extension dynamic.
Sub MergeDataFromWorkbooks()
'DECLARE AND SET VARIABLES
Dim wbk As Workbook
Dim wbk1 As Workbook
Set wbk1 = ThisWorkbook
Dim Filename As String
Dim Path As String
Path = "C:\Users\milans\Desktop\Test excel combine work\" 'CHANGE PATH
Filename = Dir(Path & "*.xls")
'--------------------------------------------
'OPEN EXCEL FILES
Do While Len(Filename) > 0 'IF NEXT FILE EXISTS THEN
Set wbk = Workbooks.Open(Path & Filename)
wbk.Activate
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Book1.xlsm").Activate
Application.ScreenUpdating = False
Dim lr As Double
lr = wbk1.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Sheet1").Select
Cells(lr + 1, 1).Select
ActiveSheet.Paste
wbk.Close True
Filename = Dir
Loop
MsgBox "All the files are copied and pasted in Book1."
End Sub
Open in new window