Macro to Copy a worksheet from an unopened workbook to a worksheet in an opened workbook

[pincivma]

HI there

I believe that is easily solved. All I want is an easy macro to do following. Any help would be appreciated.

I have a workbook that is stored in "C:\users\Documents\my name\ Book1" This workbook is not opened
I have an open workbook that I want to store the macro.
So what the macro does is open the Book1 workbook copy all the contents from Sheet1 to the opened workbook in sheet1

Note: The open workbook could have different file names.

Thanks

 

[mumps]

This macro assumes the source file has an "xlsx" extension. Change it to suit your needs.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Set wkbSource = Workbooks.Open("C:\users\Documents\my name\Book1.[COLOR="#FF0000"]xlsx[/COLOR]")
    With wkbSource
        .Sheets("Sheet1").UsedRange.Copy wkbDest.Sheets("Sheet1").Cells(1, 1)
        .Close savechanges:=False
    End With
    Application.ScreenUpdating = True
End Sub

 

[pincivma]

Hi again mumps
I tried your macro and it worked great. Thank you so much.

 

[mumps]

You are very welcome.