using an inputbox to open a file and copy worksheets to another file

[rholdren]

I have been all over the place with this one. What I'm looking for is a small piece of code to do the following. I get a monthly file with the turnover from departments from multiple states. I the files are stored on the Y:drive Every month the send the file in with a slightly different name example. Jan16 or January 2017 or 010117. I have tried a variety of things and decided it would be better if I could just use an inputbox to select the file, open the file, copy the four sheets i need from that file into the Master Rentention file (Ohio, Arkansas, Tennessee, and Texas) I don't mind the manual process but i have someone who puts the data together for me when I'm not available and (trust me) they need a button to click. Any thoughts? Your help is greatly appreciated.

 

[Fluff]

How about

Sub OpenFile()

    Dim Fname As String
    Dim Wbk As Workbook
    
    Fname = Application.GetOpenFilename(FileFilter:="xls Files (*.xls*), *.xls*", Title:="Select a file", MultiSelect:=False)
    If Fname = "False" Then
        MsgBox "no file selected"
        Exit Sub
    Else
        Set Wbk = Workbooks.Open(Fname)
        Wbk.Sheets(Array("Sheet1", "sheet2", "sheet3")).Copy before:=ThisWorkbook.Sheets(1)
        Wbk.Close , False
    End If
End Sub

This needs to go in the Master retention workbook.

 

[rholdren]

That was perfect thanks. Sometimes i make things far more complicated than it should be.

 

[Fluff]

Glad to help & thanks for the feedback