PDA

View Full Version : Solved: If value found, copy range macro help!



perbags
06-17-2010, 03:23 AM
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
06-17-2010, 03:27 AM
...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
06-17-2010, 03:30 AM
Unresolved is in a drop down colum list so it shows on it own....

GTO
06-17-2010, 04:24 AM
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
06-17-2010, 04:43 AM
Thats for the coding GTO, I ran the Marco but got the following error msg attached..... any ideas?

GTO
06-17-2010, 05:05 AM
What error msg(?) and at what line?