VBA Code copy in sheet from closed workbook.

[jakobt]

Have a open workbook called Summary.xlsx. But this could in principle be all current selected workbooks.

Want to create a macro:
1) to open another workbook called Accounts, saved in folder C:\
2) Copy sheet called MD in workbook called Accounts
3) Insert it in workbook summary (or any other workbook which was initially selected.
4) Closed workbook called Accounts
5) Select cell A1 in The first selected workbook.

 

[Yongle]

Try this
- path in red should be full path to the file

Sub CopySheet()
    Application.ScreenUpdating = False
    Dim acs As Workbook, Book As Workbook
    Set Book = ActiveWorkbook
[COLOR=#a9a9a9]'1) to open another workbook called Accounts, saved in folder C:\[/COLOR]
    Set acs = Workbooks.Open("[COLOR=#ff0000]C:\Accounts.xlsx[/COLOR]")
[COLOR=#a9a9a9]'2) Copy sheet called MD in workbook called Accounts[/COLOR]
[COLOR=#a9a9a9]'3) Insert it in workbook summary (or any other workbook which was initially selected[/COLOR]
    acs.Sheets("MD").Copy before:=Book.Sheets(1)
[COLOR=#a9a9a9]'4) Closed workbook called Accounts (without saving)[/COLOR]
    acs.Close False
[COLOR=#a9a9a9]'5) Select cell A1 in The first selected workbook[/COLOR]
    Range("A1").Activate
End Sub