I have the code below with the help of John H. Davis. The original sheet will always be empty with the data pasted afterwards, how can I get the code to trigger when new data is pasted in the original sheet?
Please Login or Register to view this content.
Goeie dag, Lucille Boshoff,
It is difficult to assist without seeing a sample of your Workbook which appears to have some "Named Ranges". A few solutions might be possible. I am sure that JOHN H. DAVIS may come to your rescue again, since he may remember the finer details of your original requirement.
If not, then please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.
Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Baie dandie.
Please consider:
Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .
Thanks Winson, nice to see a fellow South African here!
I attached a workbook, the 'Scheme' sheet is where the data will be pasted, thus initially it will be a blank template. When the data is pasted and an amount exceeds R4500 in columns E:G, I need it to copy to the 'Scheme - High Values' sheet. Currently it only copies the detail over when changes is made to values already on the sheet.
Sorry, but I'm not nearly as clued up with VBA as I would like to be...
Hi Lucille Boshoff,
Thank you for the feedback, and sample Workbook.
Please replace your current Code with the Code below, and let me know if that works for you.
Groete, Winon.Please Login or Register to view this content.
Hi Winson,
It works but only when you click on the amount on the 'Scheme' tab it copies it over to the 'High Values' sheet. These sheets usually contain thousands of lines. Preferably it should be copied automatically when the data is pasted....
Hi Lucille Boshoff,
Sorry for getting back to you so late. I have tried to make your code to work the way expected, but Excel would not join the "Party".
Who is Winson?LOL!!! Just kidding, people call me very funny names!
The "problem" with the code as you have it, is that it will only "fire" when you make manual changes in any Cell/s within the Range("E:G").
It could also allow for any inadvertently duplications to be accommodated, without an alert of same! Not good.
Bad news, you cannot have it your way, as woman usually wants.
Good news, as I am from "Mars", the attached sample Workbook demonstrates a fail proof workaround, which does what is possible, including the removal of "Duplications", should there be any.
Kind Regards.
Last edited by Winon; 08-13-2016 at 02:15 AM.
Hi Winon!
Apologies for misspelling your name, I need to reply on my cellphone as our company's anti-virus blocks replying on threads and the auto-correct changed your name again this morning
Thanks for being a "Martian"
. I tried advanced filtering etc. but cannot duplicate yours...
Please help?
Hi,
Perhaps this will meet your needs.
Please Login or Register to view this content.
Last edited by xlnitwit; 08-15-2016 at 04:05 AM. Reason: Incorrect column
Don
Please remember to mark your thread 'Solved' when appropriate.
Thanks xlnitwit,
This also works great, but only columns E,F & G with values >= R4500 should copy over. If a row has a value >= R4500 in columns H or I only, the row should not copy over... Thus columns H & I can be ignored.
I have amended the code I posted previously to only check those three columns.
Perfect!!
BIG-FAT-THANK-YOU_A6-1.png
You are most welcome. And thank you for the tip as well.
Hi Lucille Boshoff,
Thank you for the feedback, and for adding to my Reputation. Much appreciated!
In your Post#5 you also state;The Code xlnitwit provided you is good, but unfortunately it will not work if you did happen to Paste any Data in Sheets("Scheme"). Also if you inadvertently enter any duplicate Data in Sheets("Scheme"), xlnitwit's Code would gladly play along. Please try to do that, and you will see what I am getting at, since it is quite possible that it could happen with the, "These sheets usually contain thousands of lines."...These sheets usually contain thousands of lines.Preferably it should be copied automatically when the data is pasted....
To copy the Sheet Scheme - High Values to any other Sheet, you could use Code something like below.
Please try the attached revised Workbook.Please Login or Register to view this content.
Regards.
P.S. @ xlnitwit, Please, I am not criticizing you or your contribution, it is just that Copy & Paste does not "Fire" a check in the WorkSheet_Change Event for Duplications.
Last edited by Winon; 08-15-2016 at 11:29 AM. Reason: Corrected stupid comment. pointed out by xlnitwit!
With respect, that is not correct. Copy and paste will trigger a Worksheet_Change event since you are changing cells.
My apologies xlnitwit, you are correct!
This line:Should read:P.S. @ xlnitwit, Please, I am not criticizing you or your contribution, it is just that Copy & Paste does not "Fire" a WorkSheet_Change Event.With your permission I would like to Edit that error, please?P.S. @ xlnitwit, Please, I am not criticizing you or your contribution, it is just that Copy & Paste does not "Fire" a check in the WorkSheet_Change Event for Duplications.
Regards.
Be my guest.
I admit I did not address the issue of duplicates since it was never mentioned by Lucille.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks