Reputation: 443
Hey I am getting this error in excel: application defined of object defined error
Private Sub CommandButton1_Click()
Dim r1 As Range
Dim r2 As Range
Dim a As Integer
Dim b As Integer
b = 1
Set r2 = Range("a1:a100")
For Each r1 In r2.Cells
If r1.Value = "hummer1" Then
a = r1.Row
Range(Cells(a, 1), Cells(a, 5)).Copy
Sheets("Sheet2").Range(Cells(b, 1), Cells(b, 1)).Paste "line with the error"
b = b + 1
End If
Next r1
End Sub
it looks very simple but I was unable to figure out what is the problem
Upvotes: 0
Views: 1490
Reputation: 19737
No way to check but I think Paste
method is available on Worksheets Object
only.
So to make it work remove it and execute direct copy like this:
Range(Cells(a, 1), Cells(a, 5)).Copy Sheets("Sheet2").Range(Cells(b, 1), Cells(b, 1))
Also, since r2
is declared as Range
object, your For Each Loop
should be as simple as:
For Each r1 In r2
'~~> rest of code here
Next
Upvotes: 0
Reputation: 1826
This should solve your problem:
Private Sub CommandButton1_Click()
Dim r1 As Range
Dim r2 As Range
Dim a As Integer
Dim b As Integer
b = 1
Set r2 = Range("a1:a100")
For Each r1 In r2.Cells
If r1.Value = "hummer1" Then
a = r1.Row
Range(Cells(a, 1), Cells(a, 5)).Copy Destination:=Sheets("Sheet2").Range("A" & b)
b = b + 1
End If
Next r1
End Sub
Upvotes: 0
Reputation: 31181
It is important to mention worksheets to have clear code. The following works provided you do not use formula and do not want updates on the second worksheet:
Dim r1 As Range
Dim r2 As Range
Dim a As Integer
Dim b As Integer
b = 1
Dim wkSheet1 As Worksheet
Dim wkSheet2 As Worksheet
Set wkSheet1 = ThisWorkbook.Worksheets("Sheet1")
Set wkSheet2 = ThisWorkbook.Worksheets("Sheet2")
Set r2 = wkSheet1.Range("a1:a100")
For Each r1 In r2.Cells
If r1.Value = "hummer1" Then
a = r1.Row
wkSheet1.Range(wkSheet1.Cells(a, 1), wkSheet1.Cells(a, 5)).Copy
wkSheet2.Range(wkSheet2.Cells(b, 1), wkSheet2.Cells(b, 5)).PasteSpecial Paste:=xlPasteValues
b = b + 1
End If
Next r1
By the way, use "worksheets" instead of "sheets": sheets object points to both graphic sheets and usual sheets. Since you know you want to paste on a usual sheet, mentionning it make the code more readable!
Upvotes: 2