Trying to auto transfer data from one sheet to another inside the same workbook if certain

[Nitro2481]

Hello and thank you for you time. I'm new to the forum and hope someone can help me. I have 2 sheets. One for say banana band one for oranges
Banana sheet is

Please Login or Register  to view this content.

Orange sheet

Please Login or Register  to view this content.

If joe just buys a banana I want to input it into the banana sheet. If he just buys an orange I want to input it in the orange sheet but if I say y to purchasing an orange too I would like the name and number to automatically populate in the orange sheet on the next available line. I may be in the wrong sub catagory but not sure if it is a formula or VBA needed. I'll take whatever works. Please note that I'm only using oranges as an example. It some times can take time for the "orange" to be bought with the banana so it may not be in chronological order when transferred into the orange sheet.

Many thanks

[natefarm]

In the VBA window, select View - Project Explorer, and in the tree view, double-click the Banana sheet to create a code window for it. Paste in the following code:

Please Login or Register  to view this content.

[Nitro2481]

Natefarm that's brilliant. I'm not 100% on vba but I got this to work. In the formula you gave this carries over the name the number and the 3rd column "y". I tried changing the first number 3 in the formula you have me to 2 thinking that was the number of columns but that didn't help. I will be looking to do this on a few different sheets. Would you mind highlighting the variables ie what I'll need to change if I want to copy from diffent Colums and different amount of columns. If it is possible. Also if I want to pick stuff from different columns do they all have to be beside each other or is it possible to pick a and d and f if l is y for example. Thanks so much.

[natefarm]

Sorry about that. I knew better. The following will copy A:B to A. Adjust the range as needed, or for non-contiguous columns, make a copy of that line of code and adjust.

Please Login or Register  to view this content.

[Nitro2481]

That worked great. No y coming over. Last question I hope. I cannot seem to figure out the non contiguous one. Could you do one example of that for me to compare and say column e is the trigger with y. I tried changing the number 3 to 5 but that didn't seem to work. I appreciate the time your putting in.

[Nitro2481]

Actually changing the 3 to 5 did work using y. Changing the trigger from y to No doesn't seem to work. Does the trigger have to be one letter only? It's not a problem if it is.

[natefarm]

The UCase may be throwing you off. I put that in so it wouldn't matter if you entered N or n - it would still work. If you want No instead of N, be sure to put "NO" in the code, not "No".

Regarding the non-contiguous question, for example, the following would copy columns A:B to A:B, and then D:E to C:D.

Please Login or Register  to view this content.

[Nitro2481]

That's crystal clear Natefarm and I have been able to manipulate to fit my different scenarios. I am getting an error however when I try to set up this code twice. For example I want the banana sheet to auto transfer to an apple sheet or an orange sheet. I've tried copying the whole code and pasting it below the the oranges code with the necessary changes of colums and sheet names but I'm getting an ambiguous error. Is there a way of nesting a second and more versions of this code. Again thanks a mil for your help.

[natefarm]

For example I want the banana sheet to auto transfer to an apple sheet or an orange sheet.

What is the criteria for determining which sheet you want the data copied to? In the above code, if you want to copy to Apples, just change Orange to Apples. If you want it to go to one or the other based on some criteria, I need more information before I can help you any further, but maybe the following will get you started:

Please Login or Register  to view this content.

[Nitro2481]

Basically it would be
Sheet 1 bananas
Name Number Did he take oranges y/n. Did he take apples y/n

Sheet 2 oranges
Name Number (to be copied over from bananas if c = y

Sheet 3 apples
Name. Number (to be copied over from bananas if d =y

Sometimes the customer will only buy bananas maybe with orange only or maybe with apple only or maybe with both apple and orange


Hope that helps
Thanks again

[Nitro2481]

Hi Natefarm

That last code you sent me will that cover my last explanation. I'm a little confused with the I want orange bit and how to manipulate that to my scenario.

Thank you

[natefarm]

Ok, try the following:

Please Login or Register  to view this content.

[Nitro2481]

Hi Nate farm You'll be glad to hear I'm done. It took me a while but I was able to manipulate the last one for some non contiguous sheets. Thanks a mil for all your help. It's my first time on a forum. Will not be my last.

[natefarm]

I'm glad you're time was fruitful