CODEWITHSUNDEEP
vbaexcelruntime-error
ladymrt
ladymrt

Reputation: 95

Selecting last cell in Column - Error 424?

I am attempting to select the last cell in a column, copy it's value, and paste it into another workbook. In the first workbook, I've created a button in a space. Once you click that button, I want it to disappear, take the value from the other workbook (NCR Log), add 1 to the value, and paste it into the first workbook where the button used to be. I assumed this was going to be easy, but when I run the macro I get a Run-time error 424: Object Required. Does anything in here look strange? The log is just a set of columns and rows in excel... Nothing unique about it.

For Reference: NCR Log is the workbook I'm trying to get the value from. NCRDONE is the workbook I'm trying to put the value into. Column B in the log is where I want the last cell from. I4 is the cell in which I'd like the value to go in NCRDONE.

Sub tryingtoaDD()

With ActiveSheet
For Each Sh In .Shapes
    If Not Application.Intersect(Sh.TopLeftCell, .Range("H3:J5")) Is Nothing Then
        Sh.Delete
    End If
Next Sh
End With

ScreenUpdating = False

Workbooks.Open ("R:\Quality\NCR's\NCR Log\NCR Log.xlsm")

ActiveSheet.Range("B" & Cells.Rows.Count).End(xlUp).Select.Copy

Workbooks.Open ("R:\Quality\NCR's\NCR Log\NCRDONE.xlsm")

Range("I4").Select.Paste.Select


ActiveCell.Value = ActiveCell.Value + 1




ScreenUpdating = True
End Sub

Any help would be greatly appreciated!

Upvotes: 0

Views: 1327

Answers (1)

David Zemens
David Zemens

Reputation: 53623

This should do it:

Sub tryingtoaDD()

Dim WBLog as Workbook
Dim WBDone as Workbook

With ActiveSheet
For Each Sh In .Shapes
    If Not Application.Intersect(Sh.TopLeftCell, .Range("H3:J5")) Is Nothing Then
        Sh.Delete
    End If
Next Sh
End With

ScreenUpdating = False

Set WBLog = Workbooks.Open("R:\Quality\NCR's\NCR Log\NCR Log.xlsm")
Set WBDone = Workbooks.Open("R:\Quality\NCR's\NCR Log\NCRDONE.xlsm")
WBLog.ActiveSheet.Range("B" & Cells.Rows.Count).End(xlUp).Copy _
    Destination:=WBDone.ActiveSheet.Range("I4")

ActiveCell.Value = ActiveCell.Value + 1

ScreenUpdating = True
End Sub

In your original code, this will fail:

ActiveSheet.Range("B" & Cells.Rows.Count).End(xlUp).Select.Copy

Because Select is a method that doesn't return an object, hence the 424 (Object Required) error: You're trying to call a method (.Copy) from a non-object.

This line will also fail for similar reasons.

Range("I4").Select.Paste.Select

Note that the Copy method has an optional parameter, Destination which we can use to specify exactly where the paste should result. That's what I've done in the answer above.

Upvotes: 2

Related Questions