CODEWITHSUNDEEP
vbaexcel
Cr1kk0
Cr1kk0

Reputation: 83

Excel copying different cell in same row, paste to a new sheet

I'm trying to get a workbook to copy a cell value to a worksheet if the value = "Yes", for instance, Column A contains times and Column's B to G have a drop down box for yes/no. If Cell B2 has Yes, then it'll copy the time to a different sheet from A2 to the next available row in Sheet4, Column C, also if G5 has "Yes" then it'll copy the time from A5 to the next available row in Sheet4, Column H

I've been experimenting with the below code, and I'm getting stuck. It only seems to be working if I have the "Overview" sheet selected and I can't seem to get it to move across, only down.

Sub Copy()
    Dim N As Long, i As Long, j As Long
    N = Cells(Rows.Count, "C").End(xlUp).Row
    j = 4
    Set ws1 = Worksheets("Overview")
    Set ws2 = Worksheets("Sheet4")
    For i = 2 To N

        If ws1.Cells(i, "C").Value = "Yes" Then
            ws2.Cells(j, "A").Value = ws1.Cells(i, "B").Value
            j = j + 1
        End If
    Next i
End Sub

Upvotes: 0

Views: 1252

Answers (1)

izzymo
izzymo

Reputation: 936

Try this

Sub Copy()
    Dim N As Long, i As Long, j As Long

    Set ws1 = Worksheets("Overview")
    Set ws2 = Worksheets("Sheet4")

    N = ws1.Cells(Rows.Count, "C").End(xlUp).Row
    j = 4

    For Each cell In Range("B2:G" & ws1.Cells(Rows.Count, "G").End(xlUp).Row)


        If cell.Value = "Yes" Then
            ws2.Range("A" & j).Value = ws1.Range("A" & cell.Row).Value
            j = j + 1
        End If


    Next cell

End Sub

It was working only if you had Overview sheet selected because you had set the value of N to be the count the number of rows in the activesheet rather than the overview sheet(ws1). and it wasn't scanning all the columns as your code was written only for column C

Hope the above solves your problem

Upvotes: 1

Related Questions