Solved: If value found, copy range macro help!

[perbags]

Hello

Can anyone help with this as I new to Excel VBA .

What I need is Macro that will look though my ‘sheet1’, check column ‘M’ starting on row ’A7’ for the word “unresolved”, if true copy the row range A:P to ‘sheet2’, find the next available empty row starting on range ‘A7’. Then to go back to ‘Sheet1’ and delete the data that was copied.

The data that is being copied is from a drop down list's so I dont know if this will make any differance to the macro....

Thank you to anyone that can help me with this….

Regards

Perbags

[GTO]

...check ...for the word “unresolved”, ...

Is 'unresolved' by itself (so we are looking to match the whole) or in a string, like, "This matter is unresolved" ?

[perbags]

Unresolved is in a drop down colum list so it shows on it own....

[GTO]

Try:

In a Standard Module:

Option Explicit
    
Sub exa()
Dim _
wksOne      As Worksheet, _
wksTwo      As Worksheet, _
rngSource   As Range, _
rngDest     As Range
    
    With ThisWorkbook
        Set wksOne = .Worksheets("Sheet1")
        Set wksTwo = .Worksheets("Sheet2")
        
        Set rngSource = Range(wksOne.Range("M6"), wksOne.Cells(Rows.Count, "M").End(xlUp))
        
        Set rngDest = wksTwo.Cells(Rows.Count, "A").End(xlUp)
        
        If rngDest.Row < 7 Then
            Set rngDest = rngDest.Offset(7 - rngDest.Row)
        End If
        
        rngSource.AutoFilter Field:=1, Criteria1:="unresolved"
         
        With rngSource.Offset(1, -12).Resize(rngSource.Rows.Count - 1, 16) _
            .SpecialCells(xlCellTypeVisible)
            
            .Copy rngDest
            .Delete Shift:=xlUp
        End With
         
        wksOne.AutoFilterMode = False
    End With
End Sub

Hope that helps,

Mark

Edit: Forgot to delete stuff afterwards...

[perbags]

Thats for the coding GTO, I ran the Marco but got the following error msg attached..... any ideas?

[GTO]

What error msg(?) and at what line?