Replace this line of code

if rg1.value = "CCLS" then

with this

if rg1.value = "CCLS" and range("A" & rg1.row).value = "P" then

I think that should do the trick.

Big H wrote:
Bobbo,
thanks this is perfect, however would it be possible to copy the row if
columnA = "P" and clomnH = "CCLS"

thanks for your help

Harry

"bobbo" wrote in message
oups.com...
I think this should work

Sub CpyCCLS()
dim rg1 as range
dim cpyrg as range
dim dest as range
dim i as integer



for i = 1 to 200
set rg1 = Activesheet.Range("H" & i)
if rg1.value = "CCLS" then
set cpyrg = Activesheet.Range("A" & i & ":H" & i)
set dest = Worksheets("CCLS").Range("A65536").End(xlup).offse t(1,0)
cpyrg.copy
dest.pastespecial
end if
next

end sub


Big H wrote:
Hi there,

I am new to VBA, and I am wondering if its possible to do the following:

I have a range of data (dynamic), which shouldn't go any more than 200
rows,
and is within columns A:H. What i want to do is loop through the range
and
anything which has "CCLS" within column H, then copy it to sheet CCLS.
Sometimes there might not be "CCLS" within column H, so some sort of
error
code may need to be added to make the code work.

tia Harry