Thread: Create a prompt in a macro to "browse for the Source file

I have a file that is being created weekly. There is a spreadsheet that contains data I want for another excel file. I want to copy the entire worksheet and past it into another file. However the date in the file name changes weekly. Is there a way to create a prompt in the VBA to browse for the file and permit the user to select the file needing to be imported.

Thanks!

Try this

Option Explicit


Sub openfile()
    Dim sFil   As String
    Dim sTitle As String
    Dim sWb    As String
    Dim iFilterIndex As Integer
on error goto err_handler
    ' Set up list of file filters
    sFil = "Excel Files (*.xls),*.xls"
    ' Display *.xls by default
    iFilterIndex = 1
    ' Set the dialog box caption
    sTitle = "Select  File to Zip"
    ' Get the filename
    sWb = Application.GetOpenFilename(sFil, iFilterIndex, sTitle)

    Workbooks.Open Filename:=sWb
Exit Sub
err_handler:
Msgbox "No selection made"
End Sub

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

If the code is in the Report workbook then

ThisWorkBook.Sheet1.Cells(1,1).Value=sWb

If the code is in the Report workbook then

ThisWorkBook.Sheet1.Cells(1,1).Value=sWb

You can't place the line of code that I gave you into a cell.

I've reviewed your code & made some improvements.

Option Explicit


Sub openfile()
    Dim sFil As String
    Dim sTitle As String
    Dim sWb As String
    Dim iFilterIndex As Integer

    With Application
        .ScreenUpdating = False

        ' Set up list of file filters
        sFil = "Excel Files (*.xls),*.xls"
        ' Display *.xls by default
        iFilterIndex = 1
        ' Set the dialog box caption
        sTitle = "Select  File to Open"
        ' Get the filename
        On Error GoTo err_handler
        sWb = Application.GetOpenFilename(sFil, iFilterIndex, sTitle)

        Workbooks.Open Filename:=sWb
        ActiveWorkbook.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("BRT Data").Range("A1")
        ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = "Data source: " & sWb
        'close source workbook
        ActiveWorkbook.Close False

exit_proc:
        .ScreenUpdating = True
        .CutCopyMode = False
    End With
    Exit Sub
err_handler:
    MsgBox "No selection made", vbCritical, "User Cancelled"
    Resume exit_proc
End Sub