Execute Codes after Sheet Deactivated

P

Please Help

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:blush:64").Select
Selection.Copy
Range("a10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
skipblanks _
:=False, Transpose:=False

Range("n10:blush:64").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.
 
C

Chip Pearson

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)
 
P

Please Help

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:blush:64")
R.Copy
Set R = Me.Range("a10")
R.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

Set R = Me.Range("n10:blush:64")
R.Clear
Set R = Me.Range("a1")
End Sub

Chip Pearson said:
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)




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:blush:64").Select
Selection.Copy
Range("a10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
skipblanks _
:=False, Transpose:=False

Range("n10:blush:64").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.
Click to expand...
Click to expand...
 
P

Patrick Molloy

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:blush:64").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:blush:64")
R.Copy
Set R = .Range("a10")
R.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

Set R = .Range("n10:blush:64")
R.Clear
Set R = .Range("a1")
END WITH
End Sub


Please Help said:
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:blush:64")
R.Copy
Set R = Me.Range("a10")
R.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

Set R = Me.Range("n10:blush:64")
R.Clear
Set R = Me.Range("a1")
End Sub

Chip Pearson said:
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)




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:blush:64").Select
Selection.Copy
Range("a10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
skipblanks _
:=False, Transpose:=False

Range("n10:blush:64").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.
Click to expand...
Click to expand...
Click to expand...
 
P

Please Help

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 said:
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:blush:64").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:blush:64")
R.Copy
Set R = .Range("a10")
R.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

Set R = .Range("n10:blush:64")
R.Clear
Set R = .Range("a1")
END WITH
End Sub


Please Help said:
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:blush:64")
R.Copy
Set R = Me.Range("a10")
R.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

Set R = Me.Range("n10:blush:64")
R.Clear
Set R = Me.Range("a1")
End Sub

Chip Pearson said:
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

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:blush:64").Select
Selection.Copy
Range("a10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
skipblanks _
:=False, Transpose:=False

Range("n10:blush:64").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.
Click to expand...
Click to expand...
Click to expand...
 
P

Patrick Molloy

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 said:
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 said:
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:blush:64").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:blush:64")
R.Copy
Set R = .Range("a10")
R.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

Set R = .Range("n10:blush:64")
R.Clear
Set R = .Range("a1")
END WITH
End Sub


Please Help said:
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:blush:64")
R.Copy
Set R = Me.Range("a10")
R.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

Set R = Me.Range("n10:blush:64")
R.Clear
Set R = Me.Range("a1")
End Sub

:

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

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:blush:64").Select
Selection.Copy
Range("a10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
skipblanks _
:=False, Transpose:=False

Range("n10:blush:64").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.
Click to expand...
Click to expand...
Click to expand...
 
P

Please Help

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:blush:64").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:blush:64")
R.Copy
Set R = .Range("a10")
R.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

Set R = .Range("n10:blush:64")
R.Clear
Set R = .Range("a1")
End With
End Sub


Patrick Molloy said:
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 said:
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 said:
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:blush:64").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:blush:64")
R.Copy
Set R = .Range("a10")
R.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

Set R = .Range("n10:blush:64")
R.Clear
Set R = .Range("a1")
END WITH
End Sub


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:blush:64")
R.Copy
Set R = Me.Range("a10")
R.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

Set R = Me.Range("n10:blush:64")
R.Clear
Set R = Me.Range("a1")
End Sub

:

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

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:blush:64").Select
Selection.Copy
Range("a10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
skipblanks _
:=False, Transpose:=False

Range("n10:blush:64").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.
Click to expand...
Click to expand...
Click to expand...
 
P

Patrick Molloy

you could add an IF

IF Sh.Name = "blah" then

{ your code }

END IF

Please Help said:
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:blush:64").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:blush:64")
R.Copy
Set R = .Range("a10")
R.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

Set R = .Range("n10:blush:64")
R.Clear
Set R = .Range("a1")
End With
End Sub


Patrick Molloy said:
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 said:
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.

:

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:blush:64").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:blush:64")
R.Copy
Set R = .Range("a10")
R.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipblanks
_
:=False, Transpose:=False

Set R = .Range("n10:blush:64")
R.Clear
Set R = .Range("a1")
END WITH
End Sub


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:blush:64")
R.Copy
Set R = Me.Range("a10")
R.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
skipblanks _
:=False, Transpose:=False

Set R = Me.Range("n10:blush:64")
R.Clear
Set R = Me.Range("a1")
End Sub

:

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

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:blush:64").Select
Selection.Copy
Range("a10").Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone,
skipblanks _
:=False, Transpose:=False

Range("n10:blush:64").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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run-time error 1004 paste method of worksheet class failed 3
conso macro 0
Run macro from active workbook whose name keeps changing 20
lenghty code 4
Copy Paste Macro not Pasting Values when Blank 3
Simplify Code for Copy/Paste Special 11
Help again!!! 1
wrong code 1

Top