Hello everyone
I have in main Sheet a range ("A8:H" & Last Row)
I would like help with a code to transfer data from the main sheet to the result sheet based on column D cells.
If it is free from any data and deleted from the main sheet, I hope that the idea is clear
Thanks advanced for help
Does data in sheet result to be deleted before to paste cells ?
- Battle without fear gives no glory - Just try
Thanks a lot for your reply
The deletion will be performed after the data is transferred to the results sheet
Thanks advanced for help
Hum ..!
See next code and file attached
Note: There is some space in the sheets 'name in your original file: removed
Please Login or Register to view this content.
Thank you very much for this wonderful solution
The code was tested in the original file on 20000 rows and the execution time took 100 seconds
Is there a faster way if possible
Thanks advanced for help
Last edited by Joky; 01-07-2018 at 02:01 PM.
See next code
Please Login or Register to view this content.
Thanks a lot for your reply
It's my great pleasure to know a person like you
I learned something new from helping you with this
With this wonderful adjustment the execution time took 60 seconds
Is there another way to make it faster
Thanks advanced for help
It is very curious, because with the file I prepared (19000 rows) it takes around 10 s ...versus several minutes before!
Can you attach your file ?
Thanks a lot for your reply
I tried to attach my file but it is larger than the allowed limit
I will try to work out the original file and see if I could twist it to suit my issue or not
Thanks advanced for help
You should replace the "Wrap Text" and "Merge and Center" cells with "Center across Selection" to avoid headaches.
Don't know how long this takes. Try on a copy of your workbook.
Please Login or Register to view this content.
Last edited by jolivanes; 01-07-2018 at 05:38 PM. Reason: Add Application.ScreenUpdating
Any help in this topic please
The data range in the original file ("A8: BV" & Last Row)
So the execution time took 60 seconds
Is there another way to make it faster
Thanks advanced for help
Is next code faster ...?
Please Login or Register to view this content.
How long does this take? (On a copy of your workbook with all the data)
The deletion needs to be added yet. Just a try to see if it is feasible at all.Please Login or Register to view this content.
On my machine, the code in Post #10 takes 5.1 seconds and the code from Post #13 takes 2.7 seconds
Both with a data range of 18,300+ rows and 4,500+ copies/pastes
Not even enough time to go and get a cool one poured.
When I undid the formatting (Bold and size 14 to regular 11), it cut the time just about in half.
Last edited by jolivanes; 01-08-2018 at 07:49 PM.
Hi jolivanes
Thank you very much for your help. I appreciate that a lot
i treid running the Code but it came up with run-time error
Best Regards
Thanks a lot Mr.PCI for this great solution .. You are awesome and brilliant
There is a question and note
The main sheet is the first sheet, why are replaced them to be the second sheet with running of this great code
and The last point for me now is the width of the columns. Please note the width of the columns in the results sheet
Thanks a lot for your patience in this issue
Best Regards
Last edited by Joky; 01-09-2018 at 11:55 AM.
OK, Run Time Error.
Would you mind telling us a secret. Which line turned yellow?
Hi jolivanes
Thanks a lot for your reply
The first code is this error in the following line
The second code is this error in the following linePlease Login or Register to view this content.
I tried to find out why but I could notPlease Login or Register to view this content.
Best Regards
Last edited by Joky; 01-09-2018 at 01:02 PM.
Did you change sheet names? If so, change them in the code also. Make sure you don't have leading or trailing spaces in it.
Joky,
Not sure to have a clear understanding of your needs.
"The main sheet is the first sheet, why are replaced them to be the second sheet with running of this great code"
The sheets 's name are mentioned in the code, it means where ever are the sheets it alsways sheet "main" and "result"
"the width of the columns. Please note the width of the columns in the results sheet"
Is that means you want the same width for all columns: 8.43
Hello PCI
I mean, the main sheet is the first sheet ... But with the code running. The result sheet is added automatically before the main sheet
As for the width of the columns in the results sheet, they should be the same width as the columns in the main sheet
That's all ... I appreciate a lot your sharing in my issues ...
Result Sheet was deleted .... Please see the attachment below with code running
Best Regards
Last edited by Joky; 01-09-2018 at 07:49 PM.
"I mean, the main sheet is the first sheet ... But with the code running. The result sheet is added automatically before the main sheet"
OK but does the main sheet with name "main" and result sheet with name "result"
If it is, no problem with the code sent which is working the name of the sheets.
If you have a sheet called "result", without double quotation marks, in your workbook, does this not work?
Please Login or Register to view this content.
Thanks a lot Mr. PCI for your great efforts
Thanks a lot Mr. jolivanes for your great efforts
Appreciate you guys taking the time to help me learn.
It is very difficult for me to devise such amazing codes..
Best Regards
This took 0.8 seconds with data of 19,000+ and 7,500+copy/pastes.
If you don't have a sheet named "result", it'll create it.
Please Login or Register to view this content.
That's great Mr. jolivanes
Thank you very much for awesome help .. You are a champion
Last point on this topic
How to delete rows that have been moved to the result sheet from the main sheet
Please see the attachment below
Best Regards
Here a remake with columns adjustment in sheet "result"
Please Login or Register to view this content.
For the fun see next code, it should be faster ...!
Try and comment
Please Login or Register to view this content.
Thank you Mr. PCI and thank you Mr. jolivanes
In fact, all the solutions are more than wonderful and I have learned a lot from you
But the important point now is to delete the data transferred from the main paper
How so ... it's up to you
Best regards for all of you
Hi Joky.
Believe it or not, the deleting of the rows copied to "result" sheet in "main" sheet bumps the time up from +/- 1 second to =/- 18 seconds. The difference is only for the deleting.
I forgot to change the "select" in the code also. Selecting should be minimized if possible at all.
Can the data be sorted? That might speed up the deleting.
Try this, on a copy of your workbook, and you'll notice the difference.
Let us know if we can sort the data and we'll see if that makes a differencePlease Login or Register to view this content.
Hi jolivanes.
Data was deleted from the main sheet and the execution time took 18 seconds
Great solution ... Do you have other opinions
I welcome any ideas to speed up the code in anticipation of increased data
Best Regards
Last edited by Joky; 01-10-2018 at 08:25 PM.
0.5 Seconds with sorting and the same data as previously mentioned
Let me know if this makes you a happy mandorPlease Login or Register to view this content.
Hello my dear friend jolivanes
Thank you very very much for this incredible solution
That's great and exactly what I was searching for
Thanks a lot for great help all the time
I wish you happiness .... Best and best regards
Have you thoroughly checked the last code? I think you might have to replace it with this.
Check both out and make sure the proper rows are copied into sheet result.
Please Login or Register to view this content.
Hi jolivanes
You are exactly right.
In fact the words would not be enough to thank you .. You are really wonderful and awesome
Best Regards from the deep of my heart
Thank you very much for your great efforts
Last edited by Joky; 01-13-2018 at 01:10 PM.
See how next code fits into your needs, and please comment about the speed, just to understand.
Please Login or Register to view this content.
Hi PCI
I will try to study the code to lean from this magic
Best Regards from the deep of my heart
Last edited by Joky; 01-20-2018 at 07:15 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks