VBA - Object rejected

Question

I am a new learner of VBA programming. It is really so tough to figure out the error. For the following code, there is always a message of

'object rejected'

May I understand why it is happening ToT

Thank you so much!!!

Sub SelectAndCopyCellsWithValue()

    Dim SingleCell As Range

    Dim area As Range
    Set area = ActiveSheet.Range("C158", "ED158")

    For Each SingleCell In area
        If SingleCell <> 0 Then
            SingleCell.Select
            SingleCell.Value.Copy Worksheets("Sheet1").Range("A1").End(xlToRight).Offset(0, 1)
            SingleCell.Offset(-155, 0).Value.Copy Worksheets("Sheet1").Range("A2").End(xlToRight).Offset(0, 1)
            SingleCell.Offset(-154, 0).Value.Copy Worksheets("Sheet1").Range("A3").End(xlToRight).Offset(0, 1)

        End If
    Next SingleCell

End Sub

Which specific line is raising this error? I've never seen an error message that says "object rejected". Are you sure that wasn't "Object required"? — Mathieu Guindon
– Mathieu Guindon, Commented Nov 20, 2019 at 16:52

Mathieu Guindon · Accepted Answer · 2019-11-20 16:58:51Z

1

SingleCell.Value.Copy

Range.Value is a value, not an object: values don't have members that can be invoked - this instruction is illegal and raises an object required error, because, well, an object would be required in order for that .Copy member call to be made.

You want to invoke Range.Copy, so do SingleCell.Copy.

If you mean to just copy the value and not the formatting, borders, conditional formats and whatnot, then don't involve the clipboard and just write to the cells:

Dim targetSheet As Worksheet
Set targetSheet = Worksheets("Sheet1")

targetSheet.Range("A1").End(xlToRight).Offset(0, 1).Value = SingleCell.Value
targetSheet.Range("A2").End(xlToRight).Offset(0, 1).Value = SingleCell.Offset(-155, 0).Value
targetSheet.Range("A3").End(xlToRight).Offset(0, 1).Value = SingleCell.Offset(-154, 0).Value

answered Nov 20, 2019 at 16:58

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA - Object rejected

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related