Copy File Names to Destination File

[metalscuba]

Hi all,

Hope you everyone is well..

I am trying to copy/paste file name to my destination workbook's column A. Below is copying data from all workbooks from a folder and pastes them to my main file.

Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Dim LastRow As Long
    Const strPath As String = "folder of data files"
    ChDir strPath
    strExtension = Dir("*.csv*")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource
            LastRow = .Sheets("ADJUSTMENTS_EXTR").Cells.Find("Total", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Sheets("ADJUSTMENTS_EXTR").Range("A10:V" & LastRow).Copy wkbDest.Sheets("Consolidated Data").Cells(Rows.Count, "B").End(xlUp).Offset(0, 0)
            
            **'.wkbDest.Sheets("Consolidated Data").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = wkbSource.Name**
            .Close savechanges:=False
        End With
        
        
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True

There are usually 5-6 files under data folder. Aim is to while copying data, I also need each file's name for each row at my main file's column "A" alongside with data. below is what I tried. Unfortunately couldn't make it work. Thanks in advance.

.wkbDest.Sheets("Consolidated Data").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = wkbSource.Name**

 

[rollis13]

On that line of code get rid of the leading dot. With a leading dot, as the line is inserted in the macro, it will refer to the wbkSource file (With wkbSource) which is incorrect since you, instead, need to reference wkbDest.

 

[metalscuba]

Thanks for your reply though still not seeing filename.