help with vba

[22newbie]

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

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

 

[22newbie]

open visual basic editor (Alt+F11) in your active workbook, go to the insert menu, and select module. This will insert a standard module into your current project.
View attachment 66867


They look like this (Module1, 10, 11 etc.)
View attachment 66866
Paste the code I gave you into your module (or simply add it to an existing module. If you add to existing, it must not be a module behind a sheet or userform, or anything else for that matter.

I entered the function call into A1. If you drag it down like any other formula, it should automatically refer to the next row in the column for each value in A. If there is no value, B cell should be blank. Enter into whatever column you like, as long as A1 is replaced with the cell address you need.

View attachment 66869

Thank you soooo much! Really appreciate your patience and time! those worked for me.

 

[Micron]

Glad I could help. Perhaps mark this thread as solved?

 

[22newbie]

Glad I could help. Perhaps mark this thread as solved?

awesome! I just did! Thank you!!