Copy specific cells from one worksheet to another worksheet

[jos283]

Hi everyone, need some help with this VBA code!

What I want to do is copy cells from a worksheet (i.e. "Input") to another worksheet (i.e. "Output") when a specific variable is entered into a specific column.

Whenever a "1" is entered by a user (in Yes/No i.e. E, J, O etc.), I want the code to copy the matching varible under table name (in column B, G, L) to a new worksheet where these variables will form a new table for all entries with a "1" entered. I have attached an Excel sheet and the output should look like the new worksheet "Output" after the code is run. Obviously it would be blank to begin with as nothing is changed from 0 to 1 yet. The new worksheet should update automatically in real time when 0's and 1's are entered into "Input", so there shouldn't need to create a button.

Aside from the Yes/No column, there is a Low and High column for Tables 9, 10, 11. For these, it should only look the data inputted into the 8th row, i.e. AU8, AV8, AZ8, BA8, BE8, and BF8. And rather than taking only "1", these cells should take any number (even 0) as long as Low and High are different. If they are the same, then don't move either to "Output" worksheet because no numerical range is being selected.

Any help on the code is much appreciated!

The code below is what I've tried to do so far, but it obviously hasn't worked.

In Module:

Please Login or Register  to view this content.

-------
In Sheet 1 ("Input"):

Please Login or Register  to view this content.

[davesexcel]

Probably just setting it up for each target.range would work as well

Please Login or Register  to view this content.

By the way,

Please Login or Register  to view this content.

The only variable properly set as a range would be fndRng, everything else would not be considered ranges.

Please Login or Register  to view this content.

[sintek]

Hey jos283...well done...You were very close...
Try this...

Please Login or Register  to view this content.

Keep your worksheet code as is...

Edit:
As per davesexcel....all the other rng's will be declared as Variant which generally will be accepted until it bombs out somewhere.

PS...What about the sample workbook in this post...Is it the same requirement but is this the original file..
https://www.excelforum.com/excel-pro...ml#post4702759

[jos283]

Sintek -

Yes, pretty much the same thing but an extended version of it with a few extra criteria.

Thanks, I've adjusted it to suit my requirements but the code works again!

[jos283]

For the final 3 tables:

Output.png

[sintek]

Please Login or Register  to view this content.

[Winon]

Hello jos283,

Do you know if there is a way to get the output to look like this so that the table only uses two columns instead of three

That is where the problem lies. With the conditions set for the Min and Max conditions.

B.t.w. this Code of yours below;

Please Login or Register  to view this content.

Should actually read;

Please Login or Register  to view this content.

Regards.

[jos283]

Thanks, spotted that!