Move method trouble with hidden sheet

[Earl Kiosterud]

Hi folks,

Excel 2002. I have these sheets:

...... Target Sheet .... Sheets("ReceiptsR") ...

In the Worksheet_Change event for Target Sheet, I attempt to move this sheet
to just before Sheet ReceiptsR. Sheet ReceiptsR is hidden.

ActiveSheet.Move Before:=Sheets("ReceiptsR")

It sometimes moves it to after sheet ReceiptsR instead of before. If I make
ReceiptsR visible, then do the move, then make it not visible, it seems to
always work properly. That's my workaround. Nothing in knowledge base I
could find.

I also tried this end run, but it failed in the same way:

ActiveSheet.Move After=Sheets(Sheets("ReceiptsR").Index-1)

Comments?

 

[somethinglikeant]

Earl,

This should do the trick

:==========================================
Sub Mover()

Sheets("ReceiptsR").Visible = True
ActiveSheet.Move Before:=Sheets("ReceiptsR")
Sheets("ReceiptsR").Visible = False

End Sub
:==========================================

http://www.excel-ant.co.uk

 

[Earl Kiosterud]

That's what I'm doing now. When I said that I'm making it visible first, I
should have said that I'm doing it in code. I'm just wondering if anyone
can replicate this problem (perhaps in another version of Excel), or knows
why this is happening. I don't think I'm nuts (though insanity doesn't
recognize itself) -- I think it's Excel.

 

[NickHK]

Earl,
Yes, seems I can't move a WS Before: a hidden WS, only after.
Also, can't move a WS to After: the WS before the hidden WS.
Both the above result in the moved WS being after: the hidden WS.

However, you can move a hidden WS, so in 2 stages:

Dim WSToMove As Worksheet
Dim WSBefore As Worksheet
'Move after
Set WSToMove = ActiveSheet
Set WSBefore = Worksheets("Sheet3")
'Now switch these 2
WSToMove.Move after:=WSBefore
WSBefore.Move after:=WSToMove
WSToMove.Activate
'Just to see where it is
WSBefore.Visible = True

NickHK