copying rows if something is true

[habib89]

so what i want to do is copy a row if something in column B says something.. so for example if column B says "OT2" i want to copy that entire row onto another worksheet.. is this possible? if so how would i do it? Thanks!

[colofnature]

Assuming you're data is on Sheet1 and you want it on Sheet2

sub copy_if_b_is()
sheets("sheet1").select
for each c in intersect([b:b], activesheet.usedrange)
if ucase(c.value) = "OT2" then c.entireorw.copy _
sheets("Sheet2").cells(rows.count,1).end(xlup).offset(1,0)
next
end sub


C

[habib89]

wow, that's pretty awesome! thanks for the quick response... one more question, what do i do with that code? sorry, i only know pretty much up to formulas and stuff.. dont' really know the whole VBA or anything beyond spreadsheet useage.. this would be my first venture into programming with excel, however i have programmed in C++ so at least i can follow the structure pretty well

[habib89]

Assuming you're data is on Sheet1 and you want it on Sheet2

sub copy_if_b_is()
sheets("sheet1").select
for each c in intersect([b:b], activesheet.usedrange)
if ucase(c.value) = "OT2" then c.entireorw.copy _
sheets("Sheet2").cells(rows.count,1).end(xlup).offset(1,0)
next
end sub


C

when i run it, it gives me a "runtime error 424 object required" error on the "for each c in......" line... any recommendations? do i have to declare the variables somewhere?

[colofnature]

If there is no data in column B then the Intersect(...) will return Nothing, so we check first:

Please Login or Register  to view this content.

C

[habib89]

ok, this is the third time i'm posting this, IE has crashed twice before.. i have a screenshot of the problem, but i'm gonig to try not to attach and see if i can post....

anyway, thanks for the help again colofnature!

my new problem is:

runtime error '9':
subscript out of range

and in the debugger, the problem is where "c.entirerow.copy ........." starts then goes down to the next line.. maybe for whatever reason, it thinks that after the "then" statement, it has to know that c.entirerow.copy and the rest of it is one line? i dunno.. thanks!

[colofnature]

The underscore _ after rngCell.EntireRow.Copy tells the VB interpreter to treat the following line as if it was part of the current one - I could have left it all on one line, but it's awkward to read when the line goes off the right of the screen.

I've tested the code and it works OK. I've got some random data on Sheet1 in my workbook, and the code copies the specified lines to Sheet2 - the error you're getting sound like it's getting this far and is unable to find a sheet to copy it to. So here's a modified macro:

Please Login or Register  to view this content.

which will add a new sheet to the workbook to receive the filtered data. Copy the code into the VB editor, go back to Excel, activate the worksheet with your data, then press Alt+F8 and select the macro from the list. With any luck, this time it'll work!

[habib89]

The underscore _ after rngCell.EntireRow.Copy tells the VB interpreter to treat the following line as if it was part of the current one - I could have left it all on one line, but it's awkward to read when the line goes off the right of the screen.

I've tested the code and it works OK. I've got some random data on Sheet1 in my workbook, and the code copies the specified lines to Sheet2 - the error you're getting sound like it's getting this far and is unable to find a sheet to copy it to. So here's a modified macro:

Please Login or Register  to view this content.

which will add a new sheet to the workbook to receive the filtered data. Copy the code into the VB editor, go back to Excel, activate the worksheet with your data, then press Alt+F8 and select the macro from the list. With any luck, this time it'll work!

man, you're awesome! it works now! thanks so much!