if cell is=z copy entire row to another sheet

**tsioumiou** · 02-08-2011, 02:08 PM

What i am trying to do is to copy the entire row from sheet1 to sheet2 if value on column X is "suspicious". For example if X4,X8,X12 have the word "suspicious" i want to copy rows 4,8,11 on sheet2 one below the other.
One detail: i want this list to start from the 4th line on sheet2(not from the 1st)
I tried to searched the forum but i couldn't find a code for this.
I attached a test workbook to help

Thanks

**watersev** · 02-08-2011, 05:07 PM

see attachment, there are two codes as I did not clearly understand what should be the result, run "test" and "test1"

**tsioumiou** · 02-09-2011, 12:54 PM

Originally Posted by watersev

see attachment, there are two codes as I did not clearly understand what should be the result, run "test" and "test1"

First of all thanks for the reply watersev.
On the workbook that you send me the code works and looks great. Unfortunatelly when i try to add it on my workbook it gives me an error "no cells were found"

I attached you the workbook.

**tsioumiou** · 02-09-2011, 01:05 PM

Originally Posted by tsioumiou

First of all thanks for the reply watersev.
On the workbook that you send me the code works and looks great. Unfortunatelly when i try to add it on my workbook it gives me an error "no cells were found"

I attached you the workbook.

Better example of how i get the error (attached workbook aaaa.xls)

Basically the first code (test) is exactly what i need. It copies the right row but on X column on sheet2 its doesnt have the word "suspicious".It has #value for some reason

Thanks

**watersev** · 02-10-2011, 12:45 AM

see attachment, run macro "testnew". I've changed the way the code works. It makes all those calculations "behind the scenes" but shows result in respective row of sheet 2. That's why I moved them after column "X" for you to verify result. So you do not need to make those calculations on the sheet anymore. Hope you don't mind.

**tsioumiou** · 02-10-2011, 01:20 PM

That is exactly what i want!!
You are awesome

One small thing, after i run the report, on sheet2 on column H and K i see the dates. Is it possible to see the dates and the time as well?
Or if not, only the time? I know its a bit complicated because all the other cells are merged but i really hope you can help.

Thanks a million

**watersev** · 02-10-2011, 02:15 PM

see attachment, if you are happy with the outcome, please mark the thread as solved

**tsioumiou** · 02-10-2011, 04:41 PM

I am very happy. You just saved me lots of work .

Thanks

**tsioumiou** · 02-10-2011, 06:14 PM

I am really sorry for giving you a hard time now,
but i am getting "Run-time error '9' Subscript out of range"
I only added one 1 line at the bottom of the workbook as you can see (row 62 in that case) and it gives me that error. This line is very important for me because it is there when i copy this whole report, it cannot be deleted. is there a way to avoid this error?

Another thing it also gives me an error when it wont find any lines to copy(this is not so important though)

Attached the workbook

Thanks

**watersev** · 02-11-2011, 02:42 PM

please check attachment

**tsioumiou** · 02-11-2011, 04:47 PM

Thanks for the reply.
I will check on Monday and get back to you

**tsioumiou** · 02-13-2011, 11:55 PM

Seems to be working fine now.

One question : The columns on sheet1 with the calculations (Y,Z,AA,AB) are useless now since you make the calculation with VBA. Which means i can delete them right?

Also it shows me an error on this line when it doesnt find any results (not so important as i said, only if u find some free time)

Please Login or Register  to view this content.

Everything looks so good.
Thanks a lot man

**watersev** · 02-14-2011, 10:33 AM

hi, tsioumiou, yes those columns can be deleted, the code does not work with their data. I've made some changes to the code. If no suspicious transactions found it will message you about it and shut down quietly. Use this code as a final version.

PS. If we are done and you are happy with the outcome please mark the thread as solved.

**tsioumiou** · 02-14-2011, 07:40 PM

Perfect.
Thanks for your time, learned a lot from this

SOLVED

**awinash_rkdf** · 06-04-2014, 09:37 AM

sad.xlsdo something like this as same but i need some extra i have attached the file and in file i clear what i want

**watersev** · 06-04-2014, 09:46 AM

hi there, you need to start a new thread in order to follow the Forum Rules. If you consider that this thread relates to your new thread you may give a link to it.

if cell is=z copy entire row to another sheet

LinkBack

Thread Tools

Rate This Thread

Display

if cell is=z copy entire row to another sheet

Re: if cell is=z copy entire row to another sheet

Re: if cell is=z copy entire row to another sheet

Re: if cell is=z copy entire row to another sheet

Re: if cell is=z copy entire row to another sheet

Re: if cell is=z copy entire row to another sheet

Re: if cell is=z copy entire row to another sheet

Re: if cell is=z copy entire row to another sheet

Re: if cell is=z copy entire row to another sheet

Re: if cell is=z copy entire row to another sheet

Re: if cell is=z copy entire row to another sheet

Re: if cell is=z copy entire row to another sheet

Re: if cell is=z copy entire row to another sheet

Re: if cell is=z copy entire row to another sheet

Re: if cell is=z copy entire row to another sheet

Re: if cell is=z copy entire row to another sheet

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions