Pls, I need help on how to merge multiple excel workbooks into single worksheet but with demarcation. I mean I want to know where one workbook data starts and ends, preferably filling the entire first column with workbook name.
What this mean is that as each workbook is copying to the new worksheet, it will pick its workbook name and use it to fill entire first column in the new worksheet and do that to all workbooks.
I had tried the code below before but I was giving me 'compile error: Label not define' . I want to know what is wrong with the code. Or preferably a new one.
Code
Sub Button2_Click()
Dim Wkb As Workbook
Dim wbDest As Workbook, shtDest As Worksheet, source As Worksheet
Dim path As String, ThisWB As String, Filename As String
Dim CopyRng As Range, Dest As Range
Dim currLastrow As Long, prevlastrow As Long On Error GoTo err_exit Application.EnableEvents = False Application.ScreenUpdating = False
currLastrow = 2 ' Row to start on in the sheets you are copying from ThisWB = ActiveWorkbook.Name Set shtDest = ActiveWorkbook.Sheets(1) path = GetDirectory("Select a folder containing Excel files you want to merge") Filename = Dir(path & "\*.xls", vbNormal) If Len(Filename) = 0 Then Exit Sub Do Until Filename = vbNullString If Not Filename = ThisWB Then Set Wkb = Workbooks.Open(Filename:=path & "" & Filename) Set source = Wkb.Sheets(1) Set CopyRng = source.Range(source.Cells(currLastrow, 1), source.Cells(ActiveSheet.UsedRange.Rows.Count, ActiveSheet.UsedRange.Columns.Count)) Dest = shtDest.Range("B" & shtDest.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1) CopyRng.Copy Dest Wkb.Close False prevlastrow = currLastrow currLastrow = shtDest.Cells(shdest.Rows.Count, "B").End(xlUp).Row shdest.Cells(prevlastrow, "A").Resize(currLastrow - prevlastrow + 1).Value = Filename End If Filename = Dir() Loop shdest.Range("A1").Select Application.EnableEvents = True Application.ScreenUpdating = True MsgBox "Done!" Exit Sub
GoTo err_exit: Application.EnableEvents = True Application.ScreenUpdating = True
End Sub
Thanks a lot.