Excel VBA: Moving a sheet to a new workbook while maintaining worksheet object association

Question

The goal here is to set a worksheet object, then move that worksheet to a new workbook and continue to use that worksheet object. However, by moving that worksheet it appears that the worksheet object previously associated with it is lost.

To test:

Lets say we have two excel workbooks in the same folder named Book1.xlsb and Book2.xlsb each with one sheet in them named Sheet1. When we open Book1 we put in the below sub and try to move a sheet from the other using a worksheet object.

Everything "works", but the worksheet object is lost in the process, and throws an error as soon as you try to use it again. Is it possible to move the worksheet to a new workbook and not lose that worksheet object association so that it can be referenced later?

Option Explicit
Sub test()
    Dim wb1 As Workbook
    Dim wb2 As Workbook

    Dim ws As Worksheet

    Set wb1 = ThisWorkbook
    Set wb2 = Workbooks.Open("Book2.xlsb")

    Set ws = wb2.Sheets(1)

    ws.Name = "Sheet2" 'name changed to avoid conflict with the one already there
    ws.Move wb1.Sheets(1)

    MsgBox ws.Name 'this line throws an error, indicating that ws association has been lost

End Sub

Edit: If this is not possible, how can we reliably re-set that worksheet object to the one that we just moved?

No, you would have to recreate it if it's in a new Workbook. However, that's easy since you know the name of the sheet and you have the WorkBook object reference. — braX

braX braX · Accepted Answer · 2017-08-04T18:30:58

To reset the ws object:

Set ws = wb1.WorkSheets("Sheet2")

If you dont know the name, you know it was moved to the last position, right?

Set ws = wb1.WorkSheets(wb1.WorkSheets.Count)

If it was moved to the first position, then:

Set ws = wb1.WorkSheets(1)

Excel VBA: Moving a sheet to a new workbook while maintaining worksheet object association

2 Answers