Copying cells to another sheet

[Vetequk]

I would like to be able to copy a group of cells from sheet 1 to sheet 2 or 3 based on a value in another cell on sheet 1 eg
sheet 1
If Cell A1 = 24, A2=London Road (this make up an address), A5= omit

If A5 = omit I would like the address copied to sheet 2
If A5 = Complete I would like it copied to sheet 3

I have 1600 address to go through.

Thanks

Vetequk

[rylo]

Hi

And where does the next block of data start / end? Is each block 5 rows long and every 5th line has your test requirement?

Perhaps add a small example file with 4 or 5 items, or if your data has different formats/ number of rows, an example of each, and how this example data should be output.

rylo

[Vetequk]

rylo
I have attached a sample file.
What I am trying to do is filter the list so I can copy all the address that have been omitted to the list sheet, but I only need the the information in the 1st, 2nd & 5th column copied over.


Thanks

Vetequk

[JBeaucaire]

Your macro seems to work as is for "omit", so you just need to add the "complete" references and another sheet. If the 3rd sheet were called "complete", your macro could expand like so:

Please Login or Register  to view this content.

[Vetequk]

JBeaucaire
Thanks for the reply which has been very helpful, but once the data has been filtered the first cell reference could anthing from A9 to last row. I am trying to find away to assign the cell under A8 (once the data has been filtered) to a string.
This is the code so far:-

Please Login or Register  to view this content.

I have highlighed the area that I think is the problem


Thanks

Vetequk

[JBeaucaire]

I think this solves the problem, also solves the problem of what to do if there are NO "omit" or "complete" results.:

Please Login or Register  to view this content.

[Vetequk]

JBeaucaire
Thanks that worked great

Vetequk