Reputation: 69
i'm working on a macro that helps me to copy data from one worksheet to another in Excel with on conditions. I tried the following code but i got an object required error
Any help is appreciated
Sub copier()
Dim ws1 As Worksheet, ws2 As Worksheet, src As Range, dest As Range, i As Integer
Set ws1 = Worksheets("Workload - Charge de travail")
Set ws2 = Worksheets("Sheet1")
For i = 2 To ws1.Range("A1").SpecialCells(xlLastCell).Row
Set src = ws1.Range("A" & i & ":AL" & i + 50)
Set dest = ws2.Range("A" & i & ":AL" & i + 50)
If Source.Cells(i, 31).Value = "Completed - Appointment made / Complété - Nomination faite" Then
'(i,31) this is for my drop down list's condition
src.Copy Destination:=dest
dest.Value = dest.Value
End If
Next i
End Sub
Upvotes: 0
Views: 124
Reputation: 152585
There are a few problems, Source
should not be src
it should be ws1
. src.cells(i ...)
will go to the i row after the row src is already referencing. If i is 50 then it will get the result from row 100.
Also I belive you only want one row copied at a time instead of 50 rows.
Sub copier()
Dim ws1 As Worksheet, ws2 As Worksheet, src As Range, dest As Range, i As Integer
Set ws1 = Worksheets("Workload - Charge de travail")
Set ws2 = Worksheets("Sheet1")
For i = 2 To ws1.Range("A1").SpecialCells(xlLastCell).Row
Set src = ws1.Range("A" & i & ":AL" & i)
Set dest = ws2.Range("A" & i & ":AL" & i)
If ws1.Cells(i, 31).value = "Completed - Appointment made / Complété - Nomination faite" Then
'(i,31) this is for my drop down list's condition
src.Copy Destination:=dest
'dest.value = dest.value
End If
Next i
End Sub
Upvotes: 0
Reputation: 417
Salut, veuyer verifier que "Source", should not be the sheet "src".
I'm also guessing you're trying to copy just one row at a time,
Range("A" & i & ":AL" & i) which is "A2:AL2" rather than
Range("A" & i & ":AL" & i + 50) is "A2:AL52"
Also check that cells(y,31) is intended to be "AE", which is before AL (column 38).
Upvotes: 1
Reputation: 852
You use Source
instead of src
If Source.Cells(i, 31).Value = "Completed - Appointment made / Complété...
Upvotes: 0