Add each csv filename to end of each row in excel

[Lola223]

Hi,

I am using the following VBA to loop through several csv files and copy the data into a worksheet on the active workbook the code is run from.

I would like to add the filename to the end of each row so that I know which file the copied data row has come from.

Public strPath As Range

Sub CopyRange()

Application.DisplayAlerts = False

Application.ScreenUpdating = False

Dim wkbDest As Workbook

Dim wkbSource As Workbook

Set wkbDest = ThisWorkbook

Dim LastRow As Long

Set strPath = Worksheets("Combine Dataset").Range("B1")

strExtension = Dir("*.csv*")

Do While strExtension <> ""

Set wkbSource = Workbooks.Open(strPath & strExtension)

With wkbSource

LastRow = .ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

.ActiveSheet.Range("A5:K" & LastRow).Copy wkbDest.Sheets("Banking Combined").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

.Close savechanges:=False

End With

strExtension = Dir

Loop

Application.ScreenUpdating = True

End Sub

 

[Candyman8019]

perhaps should Worksheets("Combine Dataset")... be Worksheets("Combined Dataset")...

 

[Lola223]

I have uploaded a screenshot of my spreadsheet. The B1 cell reference contains a file path. You can also see the named worksheets.

 

[Candyman8019]

I have tried the syntax on my pc and it works. It shouldn't make a difference, but try getting rid of the workbook clause and use range vs cells:

Worksheets("HybridMI Combined").Range("M" & LastRow).Value = Worksheets("Combine Dataset").Range("B1").value & ".csv"

 

[Lola223]

I don't think this really doing what I need. Stepping through the code actually results in the last row in column M being populated with the file path followed by .csv by what I actually want to happen is the following:

The first csv file is opened and all the contents copied over to my template file on tab HybridMI Combined which covers columns A to L. If there are say 300 rows in the first csv file, I'd like the first 300 rows in column M to be populated with the .csv filename these rows were copied from then same again for the second csv file until all files are covered.