Copying value on row to another sheet when specific criteria met within a column

[sam103329]

Hi Everyone!

So in this instance I want to copy data to another sheet when a spesific criteria is met.

I attached an example and a desired output which in this instance is the sheets called "1" "2" and "3"

I have all my data on the sheet called "Input" but I want to extract some data from this sheet.

So for one example, when the value in column L is =>0.8 but <1.5 I want it to copy the data in column V on the "input" sheet to sheet "1" and input it into Column A

The Data must be taken as a value of the data shown on the "input" sheet as it is a formula and will not function on sheet "1" without the related data and is only required as a value

Sheet "1" doesn't require any Headers as I am going to export these after as a csv

Like wise I would then repeat this for other values, which in my attached example are

between 1.5 and 2.5 results in data being moved to Sheet "2"
between 2.5 and 3.5 results in data being moved to Sheet "3"

I also wanted to avoid using the clipboard as this would be repeated for a range of values and I feel it would be more stable without.

Any help would be greatly appreciated.

Thanks

[Olly]

You can do this really neatly, using Power Query.

Change your Source Data (on sheet 'Input') to a Table, then use that table as the source of a Power Query, which loads to your output worksheet.

The query looks like:

Please Login or Register  to view this content.

Repeat with the relevant filter values for the other output sheets.

Now you can simply click "Refresh All" and your three output sheets will automatically update, with the values from Column V, subject to the filter criteria you have specified.

It's neat, as it needs no macros / code, it uses no complex formulae, and it doesn't do any copying / pasting. As long as you keep your source data in a Table, then the source range is automatically dynamic.

See attached workbook for a worked example.

[sam103329]

Hi Olly

Thank you for your response!

I can see that is a brilliant solution and I understand it is very clean to do without macros. I understand your approach here.

The only issue is I need this operation as part of a larger macro and need to be able to create the sheets to output to then delete them after they are exported to seperate CSV sheets all in one button. I can do all the other bits in a macro but it is just the bit in question I am struggling with.

so although your suggestion is a cleaner approach, I do need a macro approach to the solution other wise I would be extremely happy with your proposed solution.

Do you have a macro based solution to this issue?

Thanks again for your help

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[sam103329]

Hi John

Thanks for this!

I can see it works perfectly on the test data sheet and will give it a shot on my actual data set to see how it works.

This is extremely useful to know for the future.

Thank you again John and thank you Olly for the time you have spent on this, It is greatly appreciated.

If all is well I will mark the thread as solved tomorrow.

Thanks again

---Further to the above---

I have now tested this on my own data set and integrated it into the macro I was wanting and it works perfectly. So this is definitely the solution.

I will now mark the thread as solved.

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback and the reps.