VBA to copy from generic Workbook to my Workbook

[jamiem4]

Hi,

I am looking for some VBA code that will allow me to run a macro to open any Workbook (getopenfilename) and copy the contents of the first sheet that opens into a sheet named 'Esub Import' in my Workbook.

Please could someone help me with this?

Thanks!

 

[mumps]

Try:

Sub CopySheet()
    Application.ScreenUpdating = False
    Dim MyFile As String, desWS As Worksheet
    Set desWS = ThisWorkbook.Sheets("Esub Import")
    MyFile = Application.GetOpenFilename("Excel files (*.xls*), *.xls*")
    If MyFile <> "" Then
        Workbooks.Open (MyFile)
        ActiveSheet.UsedRange.Copy desWS.Cells(1, 1)
        ActiveWorkbook.Close False
    End If
    Application.ScreenUpdating = True
End Sub