Apr 26 2019 08:26 AM
The Function below copies a formula result from one cell and pastes it as a value into a different cell in the same worksheet. I expected to store this Function in an .xlam file in order to avoid saving my Workbook as a macro-enabled file.
The problem I am having is that the Function works only if invoked from within an event triggered by an ActiveX or Form control, but not if invoked as a Function from within a Worksheet (it executes with no errors but doesn't actually do anything, at all).
Is there something wrong with the coding?
Option Explicit
Public Function CopyPasteValueFn(FromCell As Variant, ToCell As Variant, Optional ReturnToActiveCell As Boolean = False) As Boolean
Dim ActiveCellAddress As Variant
ActiveCellAddress = Excel.ActiveCell.Address
Excel.ActiveSheet.Range(FromCell).Select
Excel.Selection.Copy
Excel.ActiveSheet.Range(ToCell).Select
Excel.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Excel.Application.CutCopyMode = False
If ReturnToActiveCell = True Then
Excel.ActiveSheet.Range(ActiveCellAddress).Select
End If
CopyPasteValueFn = True
End Function
Apr 29 2019 08:31 AM
SolutionApr 29 2019 09:26 AM
@Jan Karel Pieterse Thank you. It looks like I will need to save my template as a macro-enabled file. Or, continue to manually copy the formula cell and paste it as a value.
Apr 30 2019 12:13 AM
Apr 30 2019 03:49 AM
Apr 29 2019 08:31 AM
Solution