Move method trouble with hidden sheet
 

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 Befo=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?

--
Earl Kiosterud
www.smokeylake.com

Move method trouble with hidden sheet
 

Earl,

This should do the trick

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

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

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

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

Move method trouble with hidden sheet
 

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.

--
Earl Kiosterud
www.smokeylake.com

How can you tell it's a politician laying dead in the road? No skid marks.

------------------------------------------------------------
"somethinglikeant" wrote in message
oups.com...
Earl,

This should do the trick

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

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

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

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

Move method trouble with hidden sheet
 

Earl,
Yes, seems I can't move a WS Befo 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

"Earl Kiosterud" wrote in message
...
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.

--
Earl Kiosterud
www.smokeylake.com

How can you tell it's a politician laying dead in the road? No skid
marks.

------------------------------------------------------------
"somethinglikeant" wrote in message
oups.com...
Earl,

This should do the trick

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

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

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

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