Macro suddenly stopped working - HELP!

Jess1234556

New Member
Joined
Jan 26, 2025
Messages
6
Office Version
  1. 365
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.
 

Attachments

  • Screenshot 2025-03-01 191527.png
    Screenshot 2025-03-01 191527.png
    71.4 KB · Views: 5
  • Screenshot 2025-03-01 191609.png
    Screenshot 2025-03-01 191609.png
    76.1 KB · Views: 5
  • Screenshot 2025-03-01 191625.png
    Screenshot 2025-03-01 191625.png
    45.8 KB · Views: 5
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
 
Upvote 0
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:


VBA 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
 
Last edited by a moderator:
Upvote 0
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 :)
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top