Riadh Saïd
Riadh Saïd

Reputation: 69

how can i resolve an object required error

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

Answers (3)

Scott Craner
Scott Craner

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

Jason K.
Jason K.

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

Bas Verlaat
Bas Verlaat

Reputation: 852

You use Source instead of src

 If Source.Cells(i, 31).Value = "Completed - Appointment made / Complété...

Upvotes: 0

Related Questions