Macro to choose a file from folder and copy and paste data into other document

[tonywatsonhelp]

Hi Everyone,

I need a macro to do the following

when run a message box pops up saying "please choose the file you wish to import"
then in some way give them the option to choose from the files in this folder: "F:\My Docs1\Monthly Data"

When they choose the file open it copy sheet1 and past it into the sheet "raw data"

please help if you can.

thanks

Tony

 

[mumps]

Try:

Sub CopySheet()
    Application.ScreenUpdating = False
    Dim flder As FileDialog
    Dim FileName As String
    Dim FileChosen As Integer
    Dim wkbSource As Workbook
    Dim wkbDest As Workbook
    Set wkbDest = ThisWorkbook
    Set flder = Application.FileDialog(msoFileDialogFilePicker)
    flder.Title = "Please choose the file you wish to import."
    flder.InitialFileName = "F:\My Docs1\Monthly Data\"
    flder.InitialView = msoFileDialogViewSmallIcons
    flder.Filters.Clear
    flder.Filters.Add "Excel Files", "*.xls*"
    FileChosen = flder.Show
    FileName = flder.SelectedItems(1)
    Set wkbSource = Workbooks.Open(FileName)
    With wkbSource
        .Sheets("Sheet1").Cells.Copy wkbDest.Sheets("raw data").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        .Close savechanges:=False
    End With
    Application.ScreenUpdating = True
End Sub