Macro is preventing copy-paste

[Shanaya2009]

Hi everyone,

I have a table with a lot of data and formulas which is causing the calculations to run slow.
Because of that, I have a macro for manual calculation in workbook and a macro to run automatic calculation in active sheet, if I change anything.

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Calculate
End Sub

This change is preventing copy-paste function in the active sheet.
How can I solve this?

I tried to insert Application.CutCopyMode = True, but it's not working.

Thanks for any help.

 

[Siddharth Rout]

Excel has an uncanny habit of clearing the clipboard when a macro runs. When there is a change in the worksheet, Worksheet_Change runs and executes ActiveSheet.Calculate which clears the clipboard.

What you can do is check if there is data in the clipboard and if there is, then do not run ActiveSheet.Calculate.

Try this

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim var As Variant
   
    '~~> Loop through the clipboard formats
    For Each var In Application.ClipboardFormats
        '~~> There is data in clipboard
        If var > -1 Then Exit Sub
    Next var
   
    ActiveSheet.Calculate
End Sub

To know more about formats see the MS Documentation XlClipboardFormat enumeration (Excel)