Move rows into another worksheet/workbook based on cell criteria

[justdave3283]

Hi All

Firstly, please let me apologise for my total lack of knowledge - This is my first foray into the world of VBA and I'm finding it extraordinarily difficult.

So, (bearing in mind that my experience is zero), could you please help. i have tried to look through previous answers, but I'm at a level where I wouldn't even know how to amend code to fit my needs.

I have a spreadsheet, columns A:Y are populated - Rows 13:1000 (potentially)

i need to move an entire row into another sheet if that row has a "Yes" in Column V.
I don't know if it makes a difference but the "Yes" in Column V will populate as a result of a formula. Also, Column V is hidden.

I'd like the row from which the data is cut to disappear, and in the new sheet, I'd like the data to paste to the next available row.

Once again, i'm sorry for my ineptitude, but I have tried really really hard and my head hurts. Please be gentle.
Happy to provide further info if needed, (but not a copy of the spreadheet unfortunately)

thanks in anticipation.

Dave

 

[ParamRay]

.
.

This can be achieved easily enough using filters. But, if you want a macro, try this as a starting point:

Sub CopyRows()

    Dim sht1 As Worksheet
    Dim sht2 As Worksheet
    Dim rang As Range
    Dim cell As Range
    Dim rnum As Long
    
    With ActiveWorkbook
        Set sht1 = .Worksheets("Sheet1")    'change as necessary
        Set sht2 = .Worksheets.Add(After:=sht1)
    End With
    
    With sht1
        Set rang = Intersect( _
            .Columns("V"), _
            .UsedRange)
    End With
    
    sht2.Name = "Yes"
    
    rnum = 1
    For Each cell In rang
        If LCase(cell.Value) = "yes" Then
            cell.EntireRow.Copy _
                Destination:=sht2.Cells(rnum, 1)
            rnum = rnum + 1
        End If
    Next cell

End Sub

 

[justdave3283]

Thanks for this. It's certainly got me further than I would have got on my own.

However, I would prefer the original data to be removed (along with the whole row) from Sheet 1 once the relevant info has been moved to sheet 2.

Is there any way this can be done?

Thanks again

Dave

 

[justdave3283]

Also, when the data moves to sheet two, a new row is created for it at the top of the sheet. I would like the data to be entered into the next available row.

thanks

Dave

 

[justdave3283]

I'm still stuck with this...any help would be gratefully appreciated

 

[SRIKANTHBENONI]

Could you please send or attach sample sheet to work on this?

 

[justdave3283]

Unfortunately, I can't do that - sensitive data and dropdowns etc. What do you specifically need to know?

 

[SRIKANTHBENONI]

Sub CopyRows()


Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim rang As Range
Dim cell As Range
Dim rnum As Long

With ActiveWorkbook
Set sht1 = .Worksheets("Sheet1") 'change as necessary
Set sht2 = .Worksheets.Add(After:=sht1)
End With

With sht1
Set rang = Intersect( _
.Columns("V"), _
.UsedRange)
End With

sht2.Name = "Yes"

rnum = 1
For Each cell In rang
If LCase(cell.Value) = "yes" Then
cell.EntireRow.Cut _
Destination:=sht2.Cells(rnum, 1)
rnum = rnum + 1
End If
Next cell


End Sub