How to add a copy of a sheet in another workbook without opening the workbook

Question

Im setting up a code that will copy the last sheet in another workbook and add it to the end of said workbook. However I do not want my code to open the workbook.

I have tried using the application.ScreenUpdating before and after my code, but it do not seem to work. I have also tried putting the screenupdating code at the beginning of the sub and at the end.

With otherwb
   Application.ScreenUpdating = False
   .Sheets(.Sheets.count).Copy After:=.Sheets(.Sheets.count)
   Application.ScreenUpdating = True
End With

Am I using the Screenupdating code wrong or is there a reason why the workbook is still opening for me?

It is impossible without opening the workbook. You need to open a workbook if you want to edit it or add a sheet. But you can do this in the background (so the user does not see it). You need to open a second instance of Excel in the background. Have a look here: stackoverflow.com/questions/579797/… — Pᴇʜ
Several users in the link you posted suggested "Application.screenupdating = False". However it is not working for me, as stated above. Any ideas? — JanAlb
no have a look at the first anwser. It show how to open a new Excel in the background so the user doesn't see it. — Pᴇʜ

iSpain17 iSpain17 · Accepted Answer · 2019-06-19T13:52:23

You cannot achieve this in VBA without opening the file.

But, you can create an Application object, with settings its visibility to false, so the user will not notice this instance running:

Dim app as Excel.Application 
app.Visible = false 
'do stuff

As to why ScreenUpdating is not working... You are halting screen update for the application instance you run the code from. Not for the application instance you want to create (to run in background invisibly).

How to add a copy of a sheet in another workbook without opening the workbook

1 Answers