Excel - activating a previous workbook after leaving it

Question

I am working on a current workbook in Excel. In the VBA, I have done the following :

Sheets("Upload File").Select
Cells.Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

So now, "Book1" is the new workbook. Once I've done what I need to do on there, I need to it then activate the original workbook, the one where I have all the VBA etc.

I close "Book1" with :

ActiveWindow.Close

But then in the next Sub in a process, I can't call any of the sheets within the active workbook :

Sheets("Upload File").Range("A1:AB65536").ClearContents

as i get a "Subscript Out Of Range" error due to the Workbook that "Upload File" sits in not actually being activated again.

Thank you for taking the time to look at this.

Cindy Meister Cindy Meister · Accepted Answer · 2016-01-27T17:31:18

The best way to deal with something like this is to work with the object model: Declare objects for the workbooks you need to keep track of and assign the workbooks to them. Such objects don't depend on what's "active" or "selected". For example:

Dim wkbOriginal as Excel.Workbook
Dim wkbNew as Excel.Workbook

Set wkbOriginal = ActiveWorkbook
'Do things here
Set wkbNew = Workbooks.Add
'Do more things
wkbNew.Close
'wkbOriginal is still accessible and can be used in your code

Excel - activating a previous workbook after leaving it

1 Answers