Printing file name to last blank column before importing to new workbook

[seacubs17]

Hello,

I'm trying to import data from several workbooks into a single one, but I'm trying to print the file name on the first blank column for every row of data. I want to do this step on the original files before importing the data to the new workbook.

The code that I have is able to paste all the information into one workbook, but I still need to create the part of the code that pastes the file name to the first blank column and I'm not sure how to do that.

I'm not good with VBA and this is my first time posting on this forum, so please let me know if I didn't publish enough information. I was wondering if any of you could be able to help me with this? Here is the code that I currently have (Thank you!)

Sub CopyRange()
Application.ScreenUpdating = False
Dim wkbDest As Workbook
Dim wkbSource As Workbook
Set wkbDest = ThisWorkbook
Dim LastRow As Long
Const strPath As String = "C:\" 'file location

ChDir strPath

strExtension = Dir("*.xlsx")
Do While strExtension <> ""
Set wkbSource = Workbooks.Open(strPath & strExtension)
With wkbSource
LastRow = .Sheets("LX02 - Kostner WIP").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
.Sheets("Sheet1Source").Range("A2:L" & LastRow).Copy wkbDest.Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
.Close savechanges:=False

End With
strExtension = Dir

Loop

Application.ScreenUpdating = True

End Sub

 

[CSmith]

Which column L?

 

[seacubs17]

All the files have the same range A:M. Column L would be were I want to paste the source file name.

 

[seacubs17]

All the files have the same range A:M. Column L would be were I want to paste the source file name.

Sorry, that was wrong. All the files have the same range A:K. Column L would be were I want to paste the source file name.

 

[CSmith]

L1 then?

 

[seacubs17]

L2, since row 1 has the table header.

 

[CSmith]

wkbDest.Sheets("Sheet1").Cells( 2, 12).value = strPath & strExtension

added right before the close

 

[seacubs17]

This is very helpful, thank you. I actually moved it before the .Sheets because it was only pasting the last file's name when I put it before the close.

Is there a code that I can use to say paste that information from L2 down to the last row of data?

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Dim LastRow As Long
    Const strPath As String = "C:\" 'file location
    
    ChDir strPath
    
    strExtension = Dir("*.xlsx")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource
            LastRow = .Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            
            wkbSource.Sheets("Sheet1Source").Cells(2, 12).Value = strPath & strExtension
            
            .Sheets("Sheet1").Range("A2:L" & LastRow).Copy wkbDest.Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            .Close savechanges:=False

        End With
        strExtension = Dir
    
    Loop
    
    Application.ScreenUpdating = True

End Sub

 

[CSmith]

is there additional data below L2? I thought you said A:K held data L2 was for the filename? can you show a breif chunk of data from that area?

 

[seacubs17]

So, the source data files have information in columns A:K, and all the files have different number of rows with data. So, I'm trying to paste the file name on column L (first blank column) for all the rows in each worksheet and not just the first row.