Can we copy a sheet from workbook1 to workbook2 with out open workbook1?

  • Thread starter Thread starter Lee
  • Start date Start date
L

Lee

Can we copy a sheet from workbook1 to workbook2 with out
open workbook1? How to do this. Thanks
Have a great weekend.
Lee
 
Lee,

Both workbooks must be open to copy a sheet from one to the
other.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
No. You can extract data from it. You can freeze screen updating, open it,
copy the sheet, close it, update the screen and no one will be the wiser.
 
What I mean is by VBA code. Is it possible?
-----Original Message-----
Lee,

Both workbooks must be open to copy a sheet from one to the
other.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com






.
Click to expand...
 
You have to open it.
But with "Application.ScreenUpdating = False" you don't notice this

This example open C:\data\ron.xls and copy a sheet to your ActiveWorkbook
After that it will close C:\data\ron.xls

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