CODEWITHSUNDEEP
vbaexcel
Riadh Saïd
Riadh Saïd

Reputation: 69

copy data from one sheet to another?

Hello i tried this following code to help me copying data from one sheet to another with one condition (copy only the lines with "Completed - Appointment made / Complété - Nomination faite" (Range(AB)).I can not see anything when i press on the button.

any help is welcome

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("A2:AL50") ' la selection des plages de donnees
    Set dest = ws2.Range("A2:AL50")


    If src.Cells(i, 31).Value = "Completed - Appointment made / Complété - Nomination faite" Then

       '(i,31) for my drop down list


       src.Copy Destination:=dest ' page source
       dest.Value = dest.Value 'destination page

  End If

Next i
End Sub

Upvotes: 0

Views: 219

Answers (1)

Joel Trauger
Joel Trauger

Reputation: 732

Posting as an answer because I feel it should be one.

To continue our discussion from the comments, you are trying to copy only the rows that contain the value "Completed - Appointment made / Complété - Nomination faite" in Column 28.

I think you could accomplish something like this by doing:

Sub copier()
    Dim ws1 As Worksheet, ws2 As Worksheet, src As Range, dest As Range, i As Integer
    Dim DestinationRow As Integer

    DestinationRow = 1
    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("A2:AL50")
        Set dest = ws2.Range("A2:AL50")

        If src.Cells(i, 28).Value = "Completed - Appointment made / Complété - Nomination faite" Then
            src.Rows(i).Copy Destination:=dest.Rows(DestinationRow)
            DestinationRow = DestinationRow + 1
        End If
    Next i
End Sub

Upvotes: 1

Related Questions