Hi friends!

Newbie here, trying hard since days to find/learn what im looking for, with no success. You are my last hope. Probably not that hard to solve for some of you. I hope.

So, I have a master worksheet with several sheets (ie sheet1, sheet2, sheet3, etc). Daily we get source files (csv, sourcefile1_date, sourcefile2_date, etc) via email, which I download, open in text editor, copy manually and paste manually into the resp. sheets in the master workbook in the first empty cell in the list of each sheet.

My question: How can I manage with VBA, with a button on the first sheet (instructions sheet) to import the data from the csv files into their resp. sheet.

Meaning, when i have the sourcefile "sourcefile1_date", how can I tell excel to look for the name of that sourcefile, compare that name with the name of sheet1, sheet2, etc, until it matches the name from the sourcefile1_date and paste it in there in the first empty cell underneath the existing list. And do that for all selected files, putting their data into the correct sheets. The names of the sourcefiles and the sheets are the same, so excel basically just needs to compare the names to find the correct sheet for the sourcefile.

I have managed to import the main csv file into the first instructions sheet (i will post the code below), but telling excel via VBA to search for the name of the rest of the csv files, by comparing their names to the names of the sheets (sheet1, sheet2, sheet3, etc.) in order to insert it there into the first empty cell...please help!

Thanks a mill upfront, dear community


Sub ImportReport()


Dim fileToOpen As Variant
Dim fileFilterPattern As String


ChDrive "C:"
ChDir "C:\User\testfiles\"


Application.ScreenUpdating = False


fileToOpen = Application.GetOpenFilename()


If fileToOpen = False Then
    'Input cancelled
    MsgBox "No file selected"


Else
    Workbooks.OpenText _
        fileName:=fileToOpen, _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=True, _
        Semicolon:=True, _
        Comma:=False, _
        Space:=False, _
        Other:=False, _
        FieldInfo _
        :=Array(Array(1, 1), Array(2, 1)), _
        TrailingMinusNumbers:=True
    Columns("A:A").EntireColumn.AutoFit
    Range("A1:B50").Select
    Selection.Copy


    Application.DisplayAlerts = False
    ActiveWindow.Close
    Application.DisplayAlerts = True
    
    'Find first empty cell in column C
    Dim FirstCell As String
    Dim i As Integer
    FirstCell = "C4"
    Range(FirstCell).Select
    Do Until ActiveCell.Value = ""
        If ActiveCell.Value = "" Then
    Exit Do
        Else
            ActiveCell.Offset(1, 0).Select
        End If
    Loop


    ActiveSheet.Paste
    
    'Jump to the first empty cell in column C again
    FirstCell = "C4"
    Range(FirstCell).Select
    Do Until ActiveCell.Value = ""
        If ActiveCell.Value = "" Then
    Exit Do
        Else
            ActiveCell.Offset(1, 0).Select
        End If
    Loop


End If


Application.ScreenUpdating = True


End Sub