VBA copy and paste based on cell value

Hi All,

I have a list of employee names, beside each name is the shift they are working. i.e DS or NS (day shift or night shift)

What I want to do is have a code look in the list and pull every row that has DS in it, and paste it into another sheet, but not to leave any spaces between rows as they are in the employee list.

I hope this can be easily done. I'm ok with vba, getting better every day!

Thanks in advance for any replies

I assummed employee names were in column A and the shift was in column B:

Please Login or Register  to view this content.

Hi, Thanks for the quick reply

I have been working on a code

Please Login or Register  to view this content.

However this keeps returning an error saying "we can't paste because the copy and paste area is not the same size", I have made sure they are exact replicas in size.

I realised I dont want to copy the whole row, jus column "B:G"

Any Ideas?

Change

Please Login or Register  to view this content.

INTO

Please Login or Register  to view this content.

I have made sure they are exact replicas in size.

But the code doesn't know that. The code thinks you are taking the entire column B:G and trying to paste it to a non-entire column. It would work if you told it to paste to A1 but not A50 (for example).

Probably would want to change to something more like this:

Please Login or Register  to view this content.

Thanks for the reply.

That works ok, but my code now seems to copy all cells, regardless of the "NS" or "DS" and is pasting them all into "DS Sheet", and relabelling them all as "DS"

Here is my code as it is now

Please Login or Register  to view this content.

Apologies, my last reply was incorrect, Its just not pasting the "NS" Values to the "NS Sheet"

Thanks for your code, but this too returns an error "Object not recognized".

I have attached an example workbook so you can see whats happening.

Thanks again for your time

You are you looping back ward? The first cell to be copied would be the last row in sheet Histogram.
Why are the other sheets layout look weird?

You should remove these lines from the body of the code. If you do not, the code will keep copying on the same row again and again.

Please Login or Register  to view this content.

Please Login or Register  to view this content.