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?

--
Earl Kiosterud
www.smokeylake.com

[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.

--
Earl Kiosterud
www.smokeylake.com

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

------------------------------------------------------------
"somethinglikeant" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[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

"Earl Kiosterud" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>