I've set up two custom views, 'TheLot' & 'Dave' I want to make sure the
'TheLot' is elected when the user moves to another sheet. So I put following
into the shee deactivate event:
ActiveWorkbook.CustomViews("TheLot").Show
Which works, but it also pulls me back to that sheet!

I may be tackling this all wrong, but what I wanted to do was to make sure
all the collumns and the autofilter was removed when the user left the sheet,
if they had used the 'Dave' custom view.

Help would be much appreciated

I bet it would be much easier to change to the custom view you want when you
activate the sheet.



Graham Y wrote:
>
> I've set up two custom views, 'TheLot' & 'Dave' I want to make sure the
> 'TheLot' is elected when the user moves to another sheet. So I put following
> into the shee deactivate event:
> ActiveWorkbook.CustomViews("TheLot").Show
> Which works, but it also pulls me back to that sheet!
>
> I may be tackling this all wrong, but what I wanted to do was to make sure
> all the collumns and the autofilter was removed when the user left the sheet,
> if they had used the 'Dave' custom view.
>
> Help would be much appreciated

--

Dave Peterson

The problem is that I need to ensure that all the data is visible when they
leave the sheet, because the data here is used mainly to supply look up data
for some forms.
I could manually remove the view by unhiding the columns and removing the
autofilter, but I thought there would be a way, of just activating the view,
I can't even find out which view is current, although I could be looking for
the autofilter.

Thank you for making me think about another route, but if anyone knows how
to do waht I was hoping to, I'd still like to know.

"Dave Peterson" wrote:

> I bet it would be much easier to change to the custom view you want when you
> activate the sheet.
>
>
>
> Graham Y wrote:
> >
> > I've set up two custom views, 'TheLot' & 'Dave' I want to make sure the
> > 'TheLot' is elected when the user moves to another sheet. So I put following
> > into the shee deactivate event:
> > ActiveWorkbook.CustomViews("TheLot").Show
> > Which works, but it also pulls me back to that sheet!
> >
> > I may be tackling this all wrong, but what I wanted to do was to make sure
> > all the collumns and the autofilter was removed when the user left the sheet,
> > if they had used the 'Dave' custom view.
> >
> > Help would be much appreciated
>
> --
>
> Dave Peterson
>

I'm not sure what breaks if you have hidden rows or columns, but how about just
showing all the columns and rows when you leave that sheet. (Drop the custom
view completely.)

Option Explicit
Private Sub Worksheet_Deactivate()
With Me
'to remove the filter and the arrows
.AutoFilterMode = False
'or to just show all the data
If .FilterMode Then
.ShowAllData
End If
.Columns.Hidden = False
End With
End Sub

Graham Y wrote:
>
> The problem is that I need to ensure that all the data is visible when they
> leave the sheet, because the data here is used mainly to supply look up data
> for some forms.
> I could manually remove the view by unhiding the columns and removing the
> autofilter, but I thought there would be a way, of just activating the view,
> I can't even find out which view is current, although I could be looking for
> the autofilter.
>
> Thank you for making me think about another route, but if anyone knows how
> to do waht I was hoping to, I'd still like to know.
>
> "Dave Peterson" wrote:
>
> > I bet it would be much easier to change to the custom view you want when you
> > activate the sheet.
> >
> >
> >
> > Graham Y wrote:
> > >
> > > I've set up two custom views, 'TheLot' & 'Dave' I want to make sure the
> > > 'TheLot' is elected when the user moves to another sheet. So I put following
> > > into the shee deactivate event:
> > > ActiveWorkbook.CustomViews("TheLot").Show
> > > Which works, but it also pulls me back to that sheet!
> > >
> > > I may be tackling this all wrong, but what I wanted to do was to make sure
> > > all the collumns and the autofilter was removed when the user left the sheet,
> > > if they had used the 'Dave' custom view.
> > >
> > > Help would be much appreciated
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Thanks for that code not seen 'With Me' before, and just using .Columns is
quicker than selecting the ranges, which is what I would have done.

thanks

"Dave Peterson" wrote:

> I'm not sure what breaks if you have hidden rows or columns, but how about just
> showing all the columns and rows when you leave that sheet. (Drop the custom
> view completely.)
>
> Option Explicit
> Private Sub Worksheet_Deactivate()
> With Me
> 'to remove the filter and the arrows
> .AutoFilterMode = False
> 'or to just show all the data
> If .FilterMode Then
> .ShowAllData
> End If
> .Columns.Hidden = False
> End With
> End Sub
>
> Graham Y wrote:
> >
> > The problem is that I need to ensure that all the data is visible when they
> > leave the sheet, because the data here is used mainly to supply look up data
> > for some forms.
> > I could manually remove the view by unhiding the columns and removing the
> > autofilter, but I thought there would be a way, of just activating the view,
> > I can't even find out which view is current, although I could be looking for
> > the autofilter.
> >
> > Thank you for making me think about another route, but if anyone knows how
> > to do waht I was hoping to, I'd still like to know.
> >
> > "Dave Peterson" wrote:
> >
> > > I bet it would be much easier to change to the custom view you want when you
> > > activate the sheet.
> > >
> > >
> > >
> > > Graham Y wrote:
> > > >
> > > > I've set up two custom views, 'TheLot' & 'Dave' I want to make sure the
> > > > 'TheLot' is elected when the user moves to another sheet. So I put following
> > > > into the shee deactivate event:
> > > > ActiveWorkbook.CustomViews("TheLot").Show
> > > > Which works, but it also pulls me back to that sheet!
> > > >
> > > > I may be tackling this all wrong, but what I wanted to do was to make sure
> > > > all the collumns and the autofilter was removed when the user left the sheet,
> > > > if they had used the 'Dave' custom view.
> > > >
> > > > Help would be much appreciated
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

Me is the object that owns the code.

If you use Me under the ThisWorkbook module, then Me is ThisWorkbook.

If you use Me under a Userform module, then Me is the userform.

In this case, Me is the sheet that owns the code.


Graham Y wrote:
>
> Thanks for that code not seen 'With Me' before, and just using .Columns is
> quicker than selecting the ranges, which is what I would have done.
>
> thanks
>
> "Dave Peterson" wrote:
>
> > I'm not sure what breaks if you have hidden rows or columns, but how about just
> > showing all the columns and rows when you leave that sheet. (Drop the custom
> > view completely.)
> >
> > Option Explicit
> > Private Sub Worksheet_Deactivate()
> > With Me
> > 'to remove the filter and the arrows
> > .AutoFilterMode = False
> > 'or to just show all the data
> > If .FilterMode Then
> > .ShowAllData
> > End If
> > .Columns.Hidden = False
> > End With
> > End Sub
> >
> > Graham Y wrote:
> > >
> > > The problem is that I need to ensure that all the data is visible when they
> > > leave the sheet, because the data here is used mainly to supply look up data
> > > for some forms.
> > > I could manually remove the view by unhiding the columns and removing the
> > > autofilter, but I thought there would be a way, of just activating the view,
> > > I can't even find out which view is current, although I could be looking for
> > > the autofilter.
> > >
> > > Thank you for making me think about another route, but if anyone knows how
> > > to do waht I was hoping to, I'd still like to know.
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > I bet it would be much easier to change to the custom view you want when you
> > > > activate the sheet.
> > > >
> > > >
> > > >
> > > > Graham Y wrote:
> > > > >
> > > > > I've set up two custom views, 'TheLot' & 'Dave' I want to make sure the
> > > > > 'TheLot' is elected when the user moves to another sheet. So I put following
> > > > > into the shee deactivate event:
> > > > > ActiveWorkbook.CustomViews("TheLot").Show
> > > > > Which works, but it also pulls me back to that sheet!
> > > > >
> > > > > I may be tackling this all wrong, but what I wanted to do was to make sure
> > > > > all the collumns and the autofilter was removed when the user left the sheet,
> > > > > if they had used the 'Dave' custom view.
> > > > >
> > > > > Help would be much appreciated
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Hi Dave,

I saw this post that you helped Graham Y on deactivated sheet. I have a
similar situation. I tried to do it by following your foot steps and I
couldn't do it.

Please help. Basically, I have an Excel file with multiple worksheets. I
want to have the macro executes the code after leaving/deactivating a
specific sheet "abc". So I place the following code in my abc sheet. The
code executes, but it would not stop executing. Please help.

Option Explicit
Private Sub Worksheet_Deactivate()
With Me
.Range("a10:a64").Copy
.Range("o10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("N10").FormulaR1C1 = _
"=IF(LEN(RC[-13])=3,CONCATENATE(""a"",RC[-13]),RC[-13])"
.Range("N10:N64").FillDown

.Range("n10:n64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("A10:O64").Sort _
Key1:=Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

.Range("o1064").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("n1064").Clear
End With
End Sub

"Dave Peterson" wrote:

> I'm not sure what breaks if you have hidden rows or columns, but how about just
> showing all the columns and rows when you leave that sheet. (Drop the custom
> view completely.)
>
> Option Explicit
> Private Sub Worksheet_Deactivate()
> With Me
> 'to remove the filter and the arrows
> .AutoFilterMode = False
> 'or to just show all the data
> If .FilterMode Then
> .ShowAllData
> End If
> .Columns.Hidden = False
> End With
> End Sub
>
> Graham Y wrote:
> >
> > The problem is that I need to ensure that all the data is visible when they
> > leave the sheet, because the data here is used mainly to supply look up data
> > for some forms.
> > I could manually remove the view by unhiding the columns and removing the
> > autofilter, but I thought there would be a way, of just activating the view,
> > I can't even find out which view is current, although I could be looking for
> > the autofilter.
> >
> > Thank you for making me think about another route, but if anyone knows how
> > to do waht I was hoping to, I'd still like to know.
> >
> > "Dave Peterson" wrote:
> >
> > > I bet it would be much easier to change to the custom view you want when you
> > > activate the sheet.
> > >
> > >
> > >
> > > Graham Y wrote:
> > > >
> > > > I've set up two custom views, 'TheLot' & 'Dave' I want to make sure the
> > > > 'TheLot' is elected when the user moves to another sheet. So I put following
> > > > into the shee deactivate event:
> > > > ActiveWorkbook.CustomViews("TheLot").Show
> > > > Which works, but it also pulls me back to that sheet!
> > > >
> > > > I may be tackling this all wrong, but what I wanted to do was to make sure
> > > > all the collumns and the autofilter was removed when the user left the sheet,
> > > > if they had used the 'Dave' custom view.
> > > >
> > > > Help would be much appreciated
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

The .pastespecial causes the _deactivate event to fire once again.

So you could sandwich application.enableevents = false and
application.enableevents = true around each of those lines or just go whole hog:

Option Explicit
Private Sub Worksheet_Deactivate()
With Me
Application.EnableEvents = False
.Range("a10:a64").Copy
.Range("o10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("N10").FormulaR1C1 = _
"=IF(LEN(RC[-13])=3,CONCATENATE(""a"",RC[-13]),RC[-13])"
.Range("N10:N64").FillDown

.Range("n10:n64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("A10:O64").Sort _
Key1:=Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

.Range("o1064").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("n1064").Clear
Application.EnableEvents = True
End With
End Sub



Please Help wrote:
>
> Hi Dave,
>
> I saw this post that you helped Graham Y on deactivated sheet. I have a
> similar situation. I tried to do it by following your foot steps and I
> couldn't do it.
>
> Please help. Basically, I have an Excel file with multiple worksheets. I
> want to have the macro executes the code after leaving/deactivating a
> specific sheet "abc". So I place the following code in my abc sheet. The
> code executes, but it would not stop executing. Please help.
>
> Option Explicit
> Private Sub Worksheet_Deactivate()
> With Me
> .Range("a10:a64").Copy
> .Range("o10").PasteSpecial _
> Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
> :=False, Transpose:=False
>
> .Range("N10").FormulaR1C1 = _
> "=IF(LEN(RC[-13])=3,CONCATENATE(""a"",RC[-13]),RC[-13])"
> .Range("N10:N64").FillDown
>
> .Range("n10:n64").Copy
> .Range("a10").PasteSpecial _
> Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
> :=False, Transpose:=False
>
> .Range("A10:O64").Sort _
> Key1:=Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
>
> .Range("o1064").Copy
> .Range("a10").PasteSpecial _
> Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
> :=False, Transpose:=False
>
> .Range("n1064").Clear
> End With
> End Sub
>
> "Dave Peterson" wrote:
>
> > I'm not sure what breaks if you have hidden rows or columns, but how about just
> > showing all the columns and rows when you leave that sheet. (Drop the custom
> > view completely.)
> >
> > Option Explicit
> > Private Sub Worksheet_Deactivate()
> > With Me
> > 'to remove the filter and the arrows
> > .AutoFilterMode = False
> > 'or to just show all the data
> > If .FilterMode Then
> > .ShowAllData
> > End If
> > .Columns.Hidden = False
> > End With
> > End Sub
> >
> > Graham Y wrote:
> > >
> > > The problem is that I need to ensure that all the data is visible when they
> > > leave the sheet, because the data here is used mainly to supply look up data
> > > for some forms.
> > > I could manually remove the view by unhiding the columns and removing the
> > > autofilter, but I thought there would be a way, of just activating the view,
> > > I can't even find out which view is current, although I could be looking for
> > > the autofilter.
> > >
> > > Thank you for making me think about another route, but if anyone knows how
> > > to do waht I was hoping to, I'd still like to know.
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > I bet it would be much easier to change to the custom view you want when you
> > > > activate the sheet.
> > > >
> > > >
> > > >
> > > > Graham Y wrote:
> > > > >
> > > > > I've set up two custom views, 'TheLot' & 'Dave' I want to make sure the
> > > > > 'TheLot' is elected when the user moves to another sheet. So I put following
> > > > > into the shee deactivate event:
> > > > > ActiveWorkbook.CustomViews("TheLot").Show
> > > > > Which works, but it also pulls me back to that sheet!
> > > > >
> > > > > I may be tackling this all wrong, but what I wanted to do was to make sure
> > > > > all the collumns and the autofilter was removed when the user left the sheet,
> > > > > if they had used the 'Dave' custom view.
> > > > >
> > > > > Help would be much appreciated
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Dave,

Thank you very much for your prompt response.

The code works and executes, but it does not execute every time I deactivate
from the sheet. How can I make it to execute every time when I deactivate
from the sheet?

Thanks.

"Dave Peterson" wrote:

> The .pastespecial causes the _deactivate event to fire once again.
>
> So you could sandwich application.enableevents = false and
> application.enableevents = true around each of those lines or just go whole hog:
>
> Option Explicit
> Private Sub Worksheet_Deactivate()
> With Me
> Application.EnableEvents = False
> .Range("a10:a64").Copy
> .Range("o10").PasteSpecial _
> Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
> :=False, Transpose:=False
>
> .Range("N10").FormulaR1C1 = _
> "=IF(LEN(RC[-13])=3,CONCATENATE(""a"",RC[-13]),RC[-13])"
> .Range("N10:N64").FillDown
>
> .Range("n10:n64").Copy
> .Range("a10").PasteSpecial _
> Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
> :=False, Transpose:=False
>
> .Range("A10:O64").Sort _
> Key1:=Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
>
> .Range("o1064").Copy
> .Range("a10").PasteSpecial _
> Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
> :=False, Transpose:=False
>
> .Range("n1064").Clear
> Application.EnableEvents = True
> End With
> End Sub
>
>
>
> Please Help wrote:
> >
> > Hi Dave,
> >
> > I saw this post that you helped Graham Y on deactivated sheet. I have a
> > similar situation. I tried to do it by following your foot steps and I
> > couldn't do it.
> >
> > Please help. Basically, I have an Excel file with multiple worksheets. I
> > want to have the macro executes the code after leaving/deactivating a
> > specific sheet "abc". So I place the following code in my abc sheet. The
> > code executes, but it would not stop executing. Please help.
> >
> > Option Explicit
> > Private Sub Worksheet_Deactivate()
> > With Me
> > .Range("a10:a64").Copy
> > .Range("o10").PasteSpecial _
> > Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
> > :=False, Transpose:=False
> >
> > .Range("N10").FormulaR1C1 = _
> > "=IF(LEN(RC[-13])=3,CONCATENATE(""a"",RC[-13]),RC[-13])"
> > .Range("N10:N64").FillDown
> >
> > .Range("n10:n64").Copy
> > .Range("a10").PasteSpecial _
> > Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
> > :=False, Transpose:=False
> >
> > .Range("A10:O64").Sort _
> > Key1:=Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
> > OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
> >
> > .Range("o1064").Copy
> > .Range("a10").PasteSpecial _
> > Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
> > :=False, Transpose:=False
> >
> > .Range("n1064").Clear
> > End With
> > End Sub
> >
> > "Dave Peterson" wrote:
> >
> > > I'm not sure what breaks if you have hidden rows or columns, but how about just
> > > showing all the columns and rows when you leave that sheet. (Drop the custom
> > > view completely.)
> > >
> > > Option Explicit
> > > Private Sub Worksheet_Deactivate()
> > > With Me
> > > 'to remove the filter and the arrows
> > > .AutoFilterMode = False
> > > 'or to just show all the data
> > > If .FilterMode Then
> > > .ShowAllData
> > > End If
> > > .Columns.Hidden = False
> > > End With
> > > End Sub
> > >
> > > Graham Y wrote:
> > > >
> > > > The problem is that I need to ensure that all the data is visible when they
> > > > leave the sheet, because the data here is used mainly to supply look up data
> > > > for some forms.
> > > > I could manually remove the view by unhiding the columns and removing the
> > > > autofilter, but I thought there would be a way, of just activating the view,
> > > > I can't even find out which view is current, although I could be looking for
> > > > the autofilter.
> > > >
> > > > Thank you for making me think about another route, but if anyone knows how
> > > > to do waht I was hoping to, I'd still like to know.
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > I bet it would be much easier to change to the custom view you want when you
> > > > > activate the sheet.
> > > > >
> > > > >
> > > > >
> > > > > Graham Y wrote:
> > > > > >
> > > > > > I've set up two custom views, 'TheLot' & 'Dave' I want to make sure the
> > > > > > 'TheLot' is elected when the user moves to another sheet. So I put following
> > > > > > into the shee deactivate event:
> > > > > > ActiveWorkbook.CustomViews("TheLot").Show
> > > > > > Which works, but it also pulls me back to that sheet!
> > > > > >
> > > > > > I may be tackling this all wrong, but what I wanted to do was to make sure
> > > > > > all the collumns and the autofilter was removed when the user left the sheet,
> > > > > > if they had used the 'Dave' custom view.
> > > > > >
> > > > > > Help would be much appreciated
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

Check to make sure you didn't turn off enableevents and leave it off.

If that's not the problem, describe the circumstances where it doesn't fire.

ps.

Add an irritating test message to your code to make sure you know if it's
firing:

> > Option Explicit
> > Private Sub Worksheet_Deactivate()
msgbox me.name & " deactivate event"
> > With Me


Please Help wrote:
>
> Dave,
>
> Thank you very much for your prompt response.
>
> The code works and executes, but it does not execute every time I deactivate
> from the sheet. How can I make it to execute every time when I deactivate
> from the sheet?
>
> Thanks.
>
> "Dave Peterson" wrote:
>
> > The .pastespecial causes the _deactivate event to fire once again.
> >
> > So you could sandwich application.enableevents = false and
> > application.enableevents = true around each of those lines or just go whole hog:
> >
> > Option Explicit
> > Private Sub Worksheet_Deactivate()
> > With Me
> > Application.EnableEvents = False
> > .Range("a10:a64").Copy
> > .Range("o10").PasteSpecial _
> > Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
> > :=False, Transpose:=False
> >
> > .Range("N10").FormulaR1C1 = _
> > "=IF(LEN(RC[-13])=3,CONCATENATE(""a"",RC[-13]),RC[-13])"
> > .Range("N10:N64").FillDown
> >
> > .Range("n10:n64").Copy
> > .Range("a10").PasteSpecial _
> > Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
> > :=False, Transpose:=False
> >
> > .Range("A10:O64").Sort _
> > Key1:=Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
> > OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
> >
> > .Range("o1064").Copy
> > .Range("a10").PasteSpecial _
> > Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
> > :=False, Transpose:=False
> >
> > .Range("n1064").Clear
> > Application.EnableEvents = True
> > End With
> > End Sub
> >
> >
> >
> > Please Help wrote:
> > >
> > > Hi Dave,
> > >
> > > I saw this post that you helped Graham Y on deactivated sheet. I have a
> > > similar situation. I tried to do it by following your foot steps and I
> > > couldn't do it.
> > >
> > > Please help. Basically, I have an Excel file with multiple worksheets. I
> > > want to have the macro executes the code after leaving/deactivating a
> > > specific sheet "abc". So I place the following code in my abc sheet. The
> > > code executes, but it would not stop executing. Please help.
> > >
> > > Option Explicit
> > > Private Sub Worksheet_Deactivate()
> > > With Me
> > > .Range("a10:a64").Copy
> > > .Range("o10").PasteSpecial _
> > > Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
> > > :=False, Transpose:=False
> > >
> > > .Range("N10").FormulaR1C1 = _
> > > "=IF(LEN(RC[-13])=3,CONCATENATE(""a"",RC[-13]),RC[-13])"
> > > .Range("N10:N64").FillDown
> > >
> > > .Range("n10:n64").Copy
> > > .Range("a10").PasteSpecial _
> > > Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
> > > :=False, Transpose:=False
> > >
> > > .Range("A10:O64").Sort _
> > > Key1:=Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
> > > OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
> > >
> > > .Range("o1064").Copy
> > > .Range("a10").PasteSpecial _
> > > Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
> > > :=False, Transpose:=False
> > >
> > > .Range("n1064").Clear
> > > End With
> > > End Sub
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > I'm not sure what breaks if you have hidden rows or columns, but how about just
> > > > showing all the columns and rows when you leave that sheet. (Drop the custom
> > > > view completely.)
> > > >
> > > > Option Explicit
> > > > Private Sub Worksheet_Deactivate()
> > > > With Me
> > > > 'to remove the filter and the arrows
> > > > .AutoFilterMode = False
> > > > 'or to just show all the data
> > > > If .FilterMode Then
> > > > .ShowAllData
> > > > End If
> > > > .Columns.Hidden = False
> > > > End With
> > > > End Sub
> > > >
> > > > Graham Y wrote:
> > > > >
> > > > > The problem is that I need to ensure that all the data is visible when they
> > > > > leave the sheet, because the data here is used mainly to supply look up data
> > > > > for some forms.
> > > > > I could manually remove the view by unhiding the columns and removing the
> > > > > autofilter, but I thought there would be a way, of just activating the view,
> > > > > I can't even find out which view is current, although I could be looking for
> > > > > the autofilter.
> > > > >
> > > > > Thank you for making me think about another route, but if anyone knows how
> > > > > to do waht I was hoping to, I'd still like to know.
> > > > >
> > > > > "Dave Peterson" wrote:
> > > > >
> > > > > > I bet it would be much easier to change to the custom view you want when you
> > > > > > activate the sheet.
> > > > > >
> > > > > >
> > > > > >
> > > > > > Graham Y wrote:
> > > > > > >
> > > > > > > I've set up two custom views, 'TheLot' & 'Dave' I want to make sure the
> > > > > > > 'TheLot' is elected when the user moves to another sheet. So I put following
> > > > > > > into the shee deactivate event:
> > > > > > > ActiveWorkbook.CustomViews("TheLot").Show
> > > > > > > Which works, but it also pulls me back to that sheet!
> > > > > > >
> > > > > > > I may be tackling this all wrong, but what I wanted to do was to make sure
> > > > > > > all the collumns and the autofilter was removed when the user left the sheet,
> > > > > > > if they had used the 'Dave' custom view.
> > > > > > >
> > > > > > > Help would be much appreciated
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson