try my change - using the WITH statement. BUT I don't think your
selection.filldown would work with this or the original code as no cells are
selected
Private Sub Worksheet_Deactivate()
Dim R As Range
WITH ThisWorkbook.Activesheet
.Range("o10:o64").Value = .Range("a10:a64").Value
Set R = .Range("N10")
ActiveCell.FormulaR1C1 = _
"=IF(LEN(RC[-13])=3,CONCATENATE(""a"",RC[-13]),RC[-13])"
Set R = .Range("N10:N64")
Selection.FillDown
Set R = .Range("n10:n64")
R.Copy
Set R = .Range("a10")
R.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False
Set R = .Rows("10:65")
R.Sort Key1:=Me.Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Set R = .Range("o10:o64")
R.Copy
Set R = .Range("a10")
R.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False
Set R = .Range("n10:o64")
R.Clear
Set R = .Range("a1")
END WITH
End Sub
"Please Help" wrote in message
...
Chip,
Thanks for the information. May be, I am not understanding you. I
changed
it as you instructed, and it's not working. When my sheet is deactivated,
the sheet that activated and deactivated started to flicking.
Below is my updated codes. Please help. Thanks.
Private Sub Worksheet_Deactivate()
Dim R As Range
Set R = Me.Range("a10:a64")
R.Copy
Set R = Me.Range("o10")
R.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False
Set R = Me.Range("N10")
ActiveCell.FormulaR1C1 = _
"=IF(LEN(RC[-13])=3,CONCATENATE(""a"",RC[-13]),RC[-13])"
Set R = Me.Range("N10:N64")
Selection.FillDown
Set R = Me.Range("n10:n64")
R.Copy
Set R = Me.Range("a10")
R.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False
Set R = Me.Rows("10:65")
R.Sort Key1:=Me.Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Set R = Me.Range("o10:o64")
R.Copy
Set R = Me.Range("a10")
R.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False
Set R = Me.Range("n10:o64")
R.Clear
Set R = Me.Range("a1")
End Sub
"Chip Pearson" wrote:
The "problem" is that when the Deactivate event occurs, that sheet is
no longer the active sheet. When you use
Range("a10:a64").Select
in a normal code module, it is equivalent to
ActiveSheet.Range("a10:a64").Select
However, in an object module like the Sheet's code module,
Range("a10:a64").Select
is equivalent to
Me.Range("a10:a64").Select
But the worksheet referenced by Me, is no longer the active sheet, the
Select fails because you are attempting to Select cells on a sheet
that is not active. This is not allowed. Rather than using Select and
Selection, reference the range directly:
Dim R As Range
Set R = Me.Range("A10:A64")
' the rest of your code using R instead of Select and Selection
It is (almost) always unnecessary to Select anything when working with
VBA. You'll find things much easier to write and maintain when you use
explicit range references.
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
On Wed, 13 May 2009 14:19:03 -0700, Please Help
wrote:
Hello all,
I have an Excel file with multiple sheets. I like to write a code to
execute a series of codes after I leave the sheet "abc". So I write the
following code in VB of Sheet "abc":
Private Sub Worksheet_Deactivate()
Range("a10:a64").Select
Selection.Copy
Range("o10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
skipblanks _
:=False, Transpose:=False
Range("N10").Select
ActiveCell.FormulaR1C1 = _
"=IF(LEN(RC[-13])=3,CONCATENATE(""a"",RC[-13]),RC[-13])"
Range("N10:N64").Select
Selection.FillDown
Range("n10:n64").Select
Selection.Copy
Range("a10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
skipblanks _
:=False, Transpose:=False
Rows("10:65").Select
Selection.Sort Key1:=Range("A11"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("o10:o64").Select
Selection.Copy
Range("a10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
skipblanks _
:=False, Transpose:=False
Range("n10:o64").Select
Selection.Clear
Range("a1").Select
end sub
When I leave/deactivate the abc sheet, I got the following error:
Run-time error '1004': Select method of Range class failed
When I debug it, it points to Range("a10:a64").Select.
Please help. How can I write a code to execute the codes after leaving
the
sheet?
Thanks.