open workbook by VBA

[CG Rosén]

Good Day,

Looking for an approach for opening a workbook from
another workbook with code, then copy a sheet in the
opened workbook and paste this in the workbook that
are in use.

Any hints are welcome.

Brgds

Gg Rosén

 

[Ron de Bruin]

Try this

Sub test()
Dim Wb1 As Workbook
Dim Wb2 As Workbook
Application.ScreenUpdating = False
Set Wb1 = ActiveWorkbook
Set Wb2 = Workbooks.Open("C:\test.xls")
Wb2.Sheets("Sheet1").copy after:= _
Wb1.Sheets(Wb1.Sheets.Count)
Wb2.Close False
Application.ScreenUpdating = True
End Sub

 

[CG Rosén]

Good Day again,

Thanks for below, seems to work, but "Wb 2" contains lot of code and when
opening
the code is running which starts with a Userform.Show. Seems that below code
stops
due to this. How to disengage the macros at opening code?

Brgds

CG Rosén

 

[Ron de Bruin]

Application.EnableEvents = False

Use this then

Sub test2()
Dim Wb1 As Workbook
Dim Wb2 As Workbook
Application.ScreenUpdating = False
Set Wb1 = ActiveWorkbook
Application.EnableEvents = False
Set Wb2 = Workbooks.Open("C:\test.xls")
Wb2.Sheets("Sheet1").Copy after:= _
Wb1.Sheets(Wb1.Sheets.Count)
Wb2.Close False
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub