I have a code that pulls all the files from a folder to the spreadsheet. It works perfectly fine. However, I would like to add a column the first 2 letters of the filenames so I can keep track of where the data is from.
Here is the code. Hopefully someone can help
Here is the code. Hopefully someone can help
VBA Code:
Sub CopyRange()
Application.ScreenUpdating = False
Dim desWS As Worksheet, srcWB As Workbook
Set desWS = ThisWorkbook.Sheets("Master")
Dim LastRow As Long
Const strPath As String = "C:\Users\test\Desktop\New folder\"
ChDir strPath
strExtension = Dir(strPath & "*.xlsx")
Do While strExtension <> ""
Set srcWB = Workbooks.Open(strPath & strExtension)
With srcWB.Sheets("Sheet2")
LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
.Range("A2:D" & LastRow).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
srcWB.Close False
strExtension = Dir
Loop
Application.ScreenUpdating = True
End Sub