Results 1 to 8 of 8

Thread: Loop within Worksheet_Deactivate() routine

  1. #1
    Acolyte GTretick's Avatar
    Join Date
    Jul 2015
    Location
    Canada
    Posts
    33
    Articles
    0
    Excel Version
    365

    Loop within Worksheet_Deactivate() routine



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 Bob Phillips; 2020-10-12 at 05:15 PM. Reason: Added code tags

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    824
    Articles
    0
    Excel Version
    Excel 2010
    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

  3. #3
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,829
    Articles
    0
    Excel Version
    O365
    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 by Bob Phillips; 2020-10-12 at 05:29 PM.

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,807
    Articles
    0
    Excel Version
    365
    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 by p45cal; 2020-10-13 at 10:51 AM.

  5. #5
    Acolyte GTretick's Avatar
    Join Date
    Jul 2015
    Location
    Canada
    Posts
    33
    Articles
    0
    Excel Version
    365
    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.

  6. #6
    Acolyte GTretick's Avatar
    Join Date
    Jul 2015
    Location
    Canada
    Posts
    33
    Articles
    0
    Excel Version
    365
    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.
    Attached Files Attached Files

  7. #7
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    824
    Articles
    0
    Excel Version
    Excel 2010
    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
    Attached Files Attached Files

  8. #8
    Acolyte GTretick's Avatar
    Join Date
    Jul 2015
    Location
    Canada
    Posts
    33
    Articles
    0
    Excel Version
    365
    That was exactly what I was looking for. Problem is solved. Thanks everyone.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •