excel - VBA - runtime error 438 object doesn't support this property using set

Question

I have a simple script that declares a few variables as a range but I am getting the error runtime error 438 object doesn't support this property - anything I'm missing?

Sub copyId()

Dim numberID As Range, column As Range, pasteId As Range

Set column = Workbooks("Book4.xlsm").Worksheets(1).Columns("C")
Set pasteId = Workbooks("Book5.xlsx").Worksheets(1).Columns("A").xlUp

For Each numberID In column
    If numberID Like "########" Or numberID Like "#########" Then numberID.Copy Destination:=pasteId
Next numberID

End Sub

标签： excelvba

Answer 1

xlUp is used with Range.End. Then, you need to determine the last cell inside the loop:

For Each numberID In column
    If numberID Like "########" Or numberID Like "#########" Then 
        With Workbooks("Book5.xlsx").Worksheets(1)
            numberID.Copy Destination:=.Cells(.Rows.Count, "A").End(xlUp).Offset(1)
        End With
    End If
Next

EDIT:

You (presumably) don't want to loop through every single cell in the column... find the last row instead:

Dim sourceBook As Workbook
Set sourceBook = Workbooks("Book4.xlsm")

Dim destBook As Workbook
Set destBook = Workbooks("Book5.xlsx")

Dim sourceSheet As Worksheet
Set sourceSheet = sourceBook.Worksheets(1)

Dim destSheet As Worksheet
Set destSheet = destBook.Worksheets(1)

With sourceSheet
    Dim lastRow As Long
    lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
End With

Dim sourceCells As Range
Set sourceCells = sourceSheet.Range("C1:C" & lastRow)

For Each numberID in sourceCells
    If numberID Like "########" Or numberID Like "#########" Then 
        With destSheet
            numberID.Copy Destination:=.Cells(.Rows.Count, "A").End(xlUp).Offset(1)
        End With
    End If
Next