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.

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.

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.

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.

Patrick,

Thanks for your helps. I tried your code, and the code executed on the
active sheet, instead of executed on the sheet that deactivated/left.

How can I change the code to be executed on the deactivated sheet?

Thanks.

"Patrick Molloy" wrote:

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.

the workbook events for both activating and deactivating worksheets have the
sheet itself as a parameter, while the sheet's deactivate event does not.
to use this do the following:

1) change the sub to

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)


2) cut and past the code to the code page for ThisWorkbook

3)
change
WITH ThisWorkbook.Activesheet
to
WITH Sh



"Please Help" wrote in message
...
Patrick,

Thanks for your helps. I tried your code, and the code executed on the
active sheet, instead of executed on the sheet that deactivated/left.

How can I change the code to be executed on the deactivated sheet?

Thanks.

"Patrick Molloy" wrote:

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.

Patrick,

Thanks for continuing to help me. Somehow, it's not working. I place the
following code in ThisWorkbook and whenever I deactivate any sheet in the
workbook, the code executes and would not stop executing. Can I only make
the code to execute only a specific sheet is deactivated? Thanks.

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Dim R As Range

With Sh

.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:=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


"Patrick Molloy" wrote:


the workbook events for both activating and deactivating worksheets have the
sheet itself as a parameter, while the sheet's deactivate event does not.
to use this do the following:

1) change the sub to

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)


2) cut and past the code to the code page for ThisWorkbook

3)
change
WITH ThisWorkbook.Activesheet
to
WITH Sh



"Please Help" wrote in message
...
Patrick,

Thanks for your helps. I tried your code, and the code executed on the
active sheet, instead of executed on the sheet that deactivated/left.

How can I change the code to be executed on the deactivated sheet?

Thanks.

"Patrick Molloy" wrote:

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.

you could add an IF

IF Sh.Name = "blah" then

{ your code }

END IF

"Please Help" wrote in message
...
Patrick,

Thanks for continuing to help me. Somehow, it's not working. I place the
following code in ThisWorkbook and whenever I deactivate any sheet in the
workbook, the code executes and would not stop executing. Can I only make
the code to execute only a specific sheet is deactivated? Thanks.

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Dim R As Range

With Sh

.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:=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


"Patrick Molloy" wrote:


the workbook events for both activating and deactivating worksheets have
the
sheet itself as a parameter, while the sheet's deactivate event does not.
to use this do the following:

1) change the sub to

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)


2) cut and past the code to the code page for ThisWorkbook

3)
change
WITH ThisWorkbook.Activesheet
to
WITH Sh



"Please Help" wrote in message
...
Patrick,

Thanks for your helps. I tried your code, and the code executed on the
active sheet, instead of executed on the sheet that deactivated/left.

How can I change the code to be executed on the deactivated sheet?

Thanks.

"Patrick Molloy" wrote:

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.