Copy rows to new sheet, based on y/n value in 1st column

[Ducksnuts]

Hi All,
I'm pulling my hair out (partly cause my VBA skills are nill, and because I know this can be easily done).

I have a single sheet that lists available items, prices, etc. When doing quotes, you simply put y/n in Col:A for each item. I then want a simple macro that will copy all of the rows with "yes" into a second sheet. Sample data with end result attached.

I did try and butcher this macro to do what I want, and whilst it does copy data across, it's not really working. Firstly as I think it is searching along a row and copy columns, whereas I need to search a column and then copy rows.

Please Login or Register  to view this content.

Thanx in advance.
sample_data.xls

[ragulduy]

Using autofilter will be quicker than looping through I think:

Please Login or Register  to view this content.

[Naveed Raza]

Try this attached file

[Ducksnuts]

Fantastic guys, thanx for the quick replies. I knew it fairly straight forward, for those who know!

Will give it a go.

Cheers,

[Naveed Raza]

Glad we could help

1. the Solution helps you, Click * to Add Reputation and leave ur comments
2. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

[Ducksnuts]

Ok, tested on a dataset......

ragulduy - It filters the data, but doesn't seem to copy it. I have changed the "sheet" name to match my data..... Though, it did actually make me think and forehead slap myself. I could just apply a manual filter to column A!

Naveed - This works great. Do have one question, my actual data has formulas in the cells. Could the macro copy the 'values' rather than the formulas?

Cheers,

[Naveed Raza]

Just replace value below code with that existing code

Please Login or Register  to view this content.

[Ducksnuts]

Fantastic.... Thanx Naveed!