Copy Paste from within FUNCTION

[beerbotboffin]

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()

Range("H6:H15").Copy
Range("I6").Select

Selection.PasteSpecial Paste:=xlPasteValues

Range("I6").Select

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.

[DaveWhiting]

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.

[beerbotboffin]

Thanks.