No announcement yet.

Copy Paste from within FUNCTION

  • Filter
  • Time
  • Show
Clear All
new posts

  • Copy Paste from within FUNCTION

    Hi All,

    Does anyone know of how to copy / paste cells from within a function (as opposed to a sub)?

    I have the following function:
    Function KMLMoveRight()


    Selection.PasteSpecial Paste:=xlPasteValues


    Application.CutCopyMode = False

    KMLMoveRight = "Done"

    End Function

    Which does not work, nor does placing the contents in a sub and calling the sub from the function. The sub will run, but not perform the actual copy / paste.

  • #2
    Re: Copy Paste from within FUNCTION

    I am not the world's foremost authority in VBA but I thought I'd try and and help as I came across a similar issue a while ago. The problem is that the purpose of functions is strictly to return values and cannot be used to make changes on the spreadsheet. The same rule is then applied to any sub called from within that function.

    Depending on what you need to do this for, it may be worth using one of the worksheet events or simply adding a command button object to the worksheet and running your code from an event of that.

    Eg: If you wanted the cells to only copy when a user types "Copy" into cell A1 you could use (in the ThisWorkbook module):
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    ' VBA is case sensitive, so use UCase to convert string to upper case if _
    '    you want to avoid case sensitivity
    If UCase(Cells(1, 1)) = "COPY" Then
        'do stuff
    End If
    End Sub
    There are other/better ways but this seems to me to be the closest solution to how you were trying to do this.


    • #3

      Re: Copy Paste from within FUNCTION