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
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