Error 1004 for range based cell copy

Question

I've looked through countless examples (and there are many to be found) of how to use Ranges to accomplish a VBA user defined function/sub to copy the value of one specified cell to another. Unfortunately, no matter which I try, I am unable to avoid Error 1004 Application-defined or object-defined error. Here is the very simple test code:

Private Sub Foobar()
On Error GoTo fooErrorHandler
Dim c1 As Range
Dim c2 As Range
Set c1 = Sheets("Sheet1").Range("D2")
Set c2 = Sheets("Sheet1").Range("B3")
c2.Value = c1.Value
Exit Sub
fooErrorHandler:
MsgBox "Error Number: " & Err.Number & vbNewLine _
                              & "Description:    " & Err.Description
End Sub

Thanks for any help/pointers!

Answer 1

Generally speaking you cannot use a Function to manipulate the worksheet object. It looks like you're trying to get around that by invoking a subroutine from the barfoo function call. I suspect that is the error... If you run the subroutine foobar manually (press F5 or run from the macro menu) it should execute without error.

I confirm that this implementation raises the error, and also test the manual call to foobar without error.

If you can better describe the ultimate goal, perhaps we can recommend a more reliable way to achieve it.