Loop within Worksheet_Deactivate() routine

GTretick

Member
Joined
Jul 29, 2015
Messages
42
Reaction score
0
Points
6
Location
Canada
Excel Version(s)
365
I've created a routine that copies a range of cells in a worksheet (U3 to AK3) and copies it to another worksheet summary page where it selects the appropriate row (information is contained in cell T3) and pastes the information there.

The routine works well when I run the macro manually but when I try to run the macro on worksheet deactivation, it runs into problems.

using F8 I've traced it to the fact that when I get to the Sheets("Cost Control").Select line, it reactives the Worksheet_Deactivate routine again midstream which fouls things up.

Can I get it to ignore the "loop" that is being caused by this within the Worksheet_Deactivate routine?

Maybe the code needs amending to tackle the problem differently?

Thanks.



Code:
Private Sub Worksheet_Deactivate()
'Moves data from detail sheet to Summary
Dim x As Integer: x = Range("T3").Value
    Range("U3:AK3").Select
    Selection.Copy
    Sheets("Cost Control").Select
    Cells(x, 2).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
     Application.CutCopyMode = False
End Sub
 
Last edited by a moderator:
It's not a loop of the deactivate routine.
You can't make a selection on a sheet that isn't active, and this event doesn't trigger until the sheet is deactivated.
Select and Selection are seldom needed.
Try this way
Code:
Private Sub Worksheet_Deactivate()
'Moves data from detail sheet to Summary
Dim x As Integer

x = Sheets("Detail").Range("T3").Value  '<-- change sheet name as required
Sheets("Detail").Range("U3:AK3").Copy   '<-- change sheet name as required
Sheets("Cost Control").Cells(x, 2).PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False

End Sub
 
You can simplify that

Code:
Private Sub Worksheet_Deactivate()
'Moves data from detail sheet to Summary
Dim x As Long

    With Me
    
        x = .Range("T3").Value
        .Range("U3:AK3").Copy
        Worksheets("Cost Control").Cells(x, 2).PasteSpecial Paste:=xlValues 
    End With
End Sub
 
Last edited:
and more:
Code:
Private Sub Worksheet_Deactivate()
Worksheets("Cost Control").Cells(Range("T3").Value, 2).Resize(, 17).Value = Range("U3:AK3").Value
End Sub
(unqualified ranges in a sheet's code-module refer to ranges on that sheet regardless of whether it's the active sheet or not)
 
Last edited:
Thanks All. I got some deadlines at work, so it will be a week or so before I can try this out but looks promising.
 
I've got it working well as a single sheet in the workbook.

This is the code I've used at the worksheet level (not the module level)
[Note I don't know how to put my code in the nice looking code boxes I see on this forum - some tips on that would be appreciated too]

Private Sub Worksheet_Deactivate()
'Moves data from detail sheet to Summary


Dim x As Integer: x = Range("T3").Value


Range("U3:AK3").Copy
Sheets("Cost Control").Cells(x, 2).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

End Sub

I run into trouble when I try to copy the worksheet the code is housed in, and duplicate it. I get a runtime error at that point.

The purpose of the excel file is to have multiple time entry worksheets that accumulate into a summary worksheet so this is a critical part of the use of the file.

I've attached a sample of the file if that helps.

Thanks.
 

Attachments

  • Timesheet.xlsm
    203.6 KB · Views: 7
The purpose of the excel file is to have multiple time entry worksheets that accumulate into a summary worksheet so this is a critical part of the use of the file.
Sounds to me as if this in the ThisWorkbook module is what you need
Code:
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    If Sh.Name <> "Cost Control" Then
        Worksheets("Cost Control").Cells(Sh.Range("T3").Value, 2).Resize(, 17).Value = Sh.Range("U3:AK3").Value
    End If
End Sub
 

Attachments

  • Timesheet_v2.xlsm
    249.8 KB · Views: 8
That was exactly what I was looking for. Problem is solved. Thanks everyone.
 
Back
Top