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.
:
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("o10
64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False
.Range("n10
64").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("o10
64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False
.Range("n10
64").Clear
End With
End Sub
:
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.
:
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