Copy-paste special macro

3 posts / 0 new
Log in or register to post comments.
Last post
7 years ago
#1
Tarjei Kirkesaether A+ 58
TK
Copy-paste special macro

Is there a workaround to get a copy + paste macro to work within a Modano workbook. The following simple macro pastes zeros in a Modano workbook but works as intended in the released version of the same workbook so I assume there is some Modano interference (potentially with the clipboard) somewhere:

Sub Cash_Tax_Simple()

    Range("RA_Tax_Calc").Select
    Selection.Copy
    
    Range("RA_Tax_Hard_Code").Select
    Selection.PasteSpecial Paste:=xlPasteValues

    Application.CutCopyMode = False

End Sub

 

7 years ago
Michael Hutchens A+ 190

Hi Tarjei,

You need to temporarily switch off Application.EnableEvents when using VBA to manipulate content within module components - there's a an example here: www.modano.com/forum/vba_modular_workbooks.

Also, can you please NEVER EVER USE EITHER 'SELECT' OR 'PASTESPECIAL' IN A VBA MACRO AGAIN, as both are bad practice and should be avoided. Here's how I'd recommend doing it instead:

Sub Cash_Tax_Simple_Best_Practice()
    
    'Declarations:
    Dim xlcalcPrior As XlCalculation
    
    'Removes Modano events capture:
    Application.EnableEvents = False
    
    'Ensures manual calculation:
    With Application
        xlcalcPrior = .Calculation
        .Calculation = xlCalculationManual
    End With
    
    'Allows for errors:
    On Error GoTo RestoreEnableEvents
    
    'Enter code here to do things to assumptions...
    ThisWorkbook.Names("RA_Tax_Hard_Code").RefersToRange.Value = ThisWorkbook.Names("RA_Tax_Calc").RefersToRange.Value
        
    'Ensures calculation and events are always restored even if errors:
RestoreEnableEvents:
        
    'Restores prior calculation method:
    Application.Calculation = xlcalcPrior

    'Restores Modano events capture:
    Application.EnableEvents = True

End Sub

M.

7 years ago
Tarjei Kirkesaether A+ 58
TK

Thank you Mike - that worked and apologies for the terrible coding!