Macro suddenly stopped working - HELP!

[Jess1234556]

I'm relatively new to macros, but I've managed to create an automated spreadsheet with a code that transfers data (the macro is triggered with a button) from multiple tabs into one specific tab. We usually do this when the task has been completed, so we move all tasks to the completed tab.

My macro was working perfectly fine until about 40 minutes ago, when it suddenly stopped working.

To clarify, when I click on a row number, and press the button, the data is apparently transferred and then removed from the current tab. However, when I check the destination tab, the data has not been transferred, but up until not long ago, it transferred over 40 rows without issues.

I've tried several things:

• I've typed EnableEvents = true in the immediate window and also added it to the code.
• I've confirmed that all other macros in the workbook are still working fine.
• I've checked the destination tab and ranges and nothing has changed.

I haven't made any changes to the code. I'm concerned because I'm testing it before putting into the actual document that will be shared with my team.

 

[NoSparks]

Please see How to Post Your VBA Code and not post pictures of your code, thanks

 

[Jess1234556]

Please see How to Post Your VBA Code and not post pictures of your code, thanks

Oh my bad! thanks for letting me know! is it possible to delete this thread and start a new one?

 

[MARK858]

Just repost the code in a reply window in this thread, or the Moderators will point out that you shouldn't create duplicate threads.

P.S. Application.EnableEvents only affects Event code

 

[Jess1234556]

I'm relatively new to macros, but I've managed to create an automated spreadsheet with a code that transfers data (the macro is triggered with a button) from multiple tabs into one specific tab. We usually do this when the task has been completed, so we move all tasks to the completed tab.

My macro was working perfectly fine until about 40 minutes ago, when it suddenly stopped working.

To clarify, when I click on a row number, and press the button, the data is apparently transferred and then removed from the current tab. However, when I check the destination tab, the data has not been transferred, but up until not long ago, it transferred over 40 rows without issues.

I've tried several things:

• I've typed EnableEvents = true in the immediate window and also added it to the code.
• I've confirmed that all other macros in the workbook are still working fine.
• I've checked the destination tab and ranges and nothing has changed.

I haven't made any changes to the code. I'm concerned because I'm testing it before putting into the actual document that will be shared with my team.

Apologies, here's the code:

Sub TransferToCompleted()
   Dim wsSource As Worksheet
   Dim wsCompleted As Worksheet
   Dim lastRowCompleted As Long
   Dim selectedRow As Range
   Dim row As Range
   Dim analystName As String
   Dim transferred As Boolean 'Keeps track of succesful transfers
   Dim skipped As Boolean
   Dim transferredCount As Integer 'count of transferred rows
   Dim deleteResponse As VbMsgBoxResult
   Dim i As Long
  
   'Optimise performance
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
  
   'Set the source worksheet
   Set wsSource = ActiveSheet
  
   'Set the Account Completed Sheet
   Set wsCompleted = ThisWorkbook.Sheets("Accounts Completed")
  
   'Set Analyst name source
   analystName = wsSource.Name
 
  
    'Check if an entire row is selected
    If Selection.Rows.Count >= 1 Then
        Set selectedRow = Selection.Rows(1) 'Get the selected row
    Else
        MsgBox "Please select the entire row.", vbExclamation, "Selection error"
        Exit Sub
    End If
   
    'Initialise transferred count
    transferredCount = 0
   
    'Initialise skipped flag
    skipped = False

    'Loop through each selected row
    For Each row In Selection.Rows
   
        'Check if the status is marked as "Complete"
        If Trim(row.Cells(1, 13).Value) = "Complete" Then
       
            'Find last row in Accouts Completed tab
            lastRowCompleted = wsCompleted.Cells(wsCompleted.Rows.Count, 1).End(xlUp).row + 1
           
            'Copy the entire row at once
            wsCompleted.Rows(lastRowCompleted).Value = row.Value
           
           
            'CRM link (column O)
            If row.Cells(1, 15).Hyperlinks.Count > 0 Then
            row.Cells(1, 15).Copy
            wsCompleted.Cells(lastRowCompleted, 15).PasteSpecial Paste:=xlPasteAll
            Application.CutCopyMode = False
               Else
                    'if it's a plain text, just copy it as it is
                    wsCompleted.Cells(lastRowCompleted, 15).Value = row.Cells(1, 15).Value
             End If
            
             'Cell formatting
             wsCompleted.Cells(lastRowCompleted, 15).Interior.Color = RGB(206, 234, 176)
             wsCompleted.Cells(lastRowCompleted, 15).Borders(xlEdgeBottom).LineStyle = xlContinuous
             wsCompleted.Cells(lastRowCompleted, 15).Borders(xlEdgeBottom).Color = RGB(0, 0, 0)
             wsCompleted.Cells(lastRowCompleted, 15).WrapText = True
            
                   
            'Populate column P with the analyst's name
            wsCompleted.Cells(lastRowCompleted, 16).Value = analystName
          
            'Increment the transferred count
            transferredCount = transferredCount + 1
           
            'Keep track of successful transfers
            transferred = True
        Else
            skipped = True 'Mark at least one row skipped
        End If
    Next row
       
        'Shows a message if any rows were skipped
        If skipped Then
            MsgBox "Some rows were not marked as 'Complete' and were skipped.", vbExclamation, "Status Error"
        End If
       
        'Clear clipboard
        Application.CutCopyMode = False
       
        'Confirmation message if at least one row was transferred
        If transferred Then
        MsgBox transferredCount & " Accounts moved succesfully."
        End If
       
        'Prompt to delete transferred rows
        If transferred Then
            For i = Selection.Rows.Count To 1 Step -1
                If Trim(Selection.Rows(i).Cells(1, 13).Value) = "Complete" Then
                    Selection.Rows(i).EntireRow.Delete
                End If
            Next i
    End If
    'Clean up - Restore settings
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

 

[Jess1234556]

Just repost the code in a reply window in this thread, or the Moderators will point out that you shouldn't create duplicate threads.

P.S. Application.EnableEvents only affects Event code

Thanks for the tip! I'm new to this form so I wasn't aware of this. I've posted my code now