I have a data sheet, which I had assigned to a worksheet variable, shData

I used code

shData.move after:=shADI

where shADI is another worksheet variable.

I just found out that doing this moves the worksheet just as I want, but the
object, shData loses it's assignment. It's not a problem, because I can just
reassign it after the move, but does anyone know why it loses the assignment?

Something to do with the internal workings of the object itself?

Thanks.

Hi Mark,

I just found out that doing this moves the worksheet just as I want, but
the
object, shData loses it's assignment.

This is not my experience.

I tried:
'=============
Public Sub Tester()
Dim shData As Worksheet
Dim shADI As Worksheet

Set shData = ThisWorkbook.Sheets("Sheet1")
Set shADI = ThisWorkbook.Sheets("Sheet3")

shData.Move after:=shADI

Debug.Print "shData", shData.Name
Debug.Print "shADI", shADI.Name

End Sub
'<<=============

The Immediate window returned:

shData Sheet1
shADI Sheet3


---
Regards,
Norman


"mark" wrote in message
...
I have a data sheet, which I had assigned to a worksheet variable, shData

I used code

shData.move after:=shADI

where shADI is another worksheet variable.

I just found out that doing this moves the worksheet just as I want, but
the
object, shData loses it's assignment. It's not a problem, because I can
just
reassign it after the move, but does anyone know why it loses the
assignment?

Something to do with the internal workings of the object itself?

Thanks.

Thanks, Norman.

Your reply made me notice that I forgot to mention an important piece of
information. shADI and shData were originally in different workbooks.

after the command:

shData.move after:=shADI

they are both in the same workbook, as desired, but the shData object has
lost its assignment.

"Norman Jones" wrote:

Hi Mark,

I just found out that doing this moves the worksheet just as I want, but
the
object, shData loses it's assignment.

This is not my experience.

I tried:
'=============
Public Sub Tester()
Dim shData As Worksheet
Dim shADI As Worksheet

Set shData = ThisWorkbook.Sheets("Sheet1")
Set shADI = ThisWorkbook.Sheets("Sheet3")

shData.Move after:=shADI

Debug.Print "shData", shData.Name
Debug.Print "shADI", shADI.Name

End Sub
'<<=============

The Immediate window returned:

shData Sheet1
shADI Sheet3

Hi Mark,

Your reply made me notice that I forgot to mention an important piece of
information. shADI and shData were originally in different workbooks.

When the sheet object variable is set, the variable is explicitly, or
implicitly, qualified by the parent workbook. Moving the sheet to another
workbook is equivalent to adding a (new) copy of the sheet in the second
workbook and deleting the (original) worksheet in the first workbook.

As the original worksheet is deleted, the corresponding object variable is
set to nothing. If, rather than moving, you had copied the sheet to the
second workbook, the initial object variable would have continued to point
to the original sheet.

In either case, the 'new' sheet will have no corresponding object variable
unless, and until, it is set to such a (new) variable.

Otherwise expressed, there are two objects: the original sheet and the
copied sheet and the object variable is set to the former, not the latter.


---
Regards,
Norman



"mark" wrote in message
...
Thanks, Norman.

Your reply made me notice that I forgot to mention an important piece of
information. shADI and shData were originally in different workbooks.

after the command:

shData.move after:=shADI

they are both in the same workbook, as desired, but the shData object has
lost its assignment.

"Norman Jones" wrote:

Hi Mark,

I just found out that doing this moves the worksheet just as I want,
but
the
object, shData loses it's assignment.

This is not my experience.

I tried:
'=============
Public Sub Tester()
Dim shData As Worksheet
Dim shADI As Worksheet

Set shData = ThisWorkbook.Sheets("Sheet1")
Set shADI = ThisWorkbook.Sheets("Sheet3")

shData.Move after:=shADI

Debug.Print "shData", shData.Name
Debug.Print "shADI", shADI.Name

End Sub
'<<=============

The Immediate window returned:

shData Sheet1
shADI Sheet3

gotcha. thanks.

for the app I'm building, it seems best to just reassign the shData object
after the move.

Thanks for the explanation.

Mark

"Norman Jones" wrote:

Otherwise expressed, there are two objects: the original sheet and the
copied sheet and the object variable is set to the former, not the latter.