Andreamary
asked on
Userform (?) for selecting specific row(s) from one spreadsheet and paste partial part of row into another spreadsheet
I have attached a sample file to show our current process:
Worksheet 1: a static inventory list
Worksheet 2: a tracking tool for select procedures from Worksheet 1 that have revisions
Current process:
In Worksheet 1, inserting "Yes" in column "X" triggers 6 cells from that row to be copied and pasted into Worksheet 2 into the next available blank row, as follows:
Column V copied into Column A
Column W copied into Column B
Column A copied into Column C
Column B copied into Column D
Column L copied into Column E
Column M copied into Column F
There are instances when the same row needs to be copied over to Worksheet 2 a second, third and fourth time (as there can be multiple revisions to the same procedure during the 2-month production cycle), which requires the user to go back to Worksheet 1 and remove and re-insert "Yes" in Column "X" each time.
I am looking for a more sophisticated solution where:
Thanks!
Andrea
EE-SampleExistingProcess.xlsm
Worksheet 1: a static inventory list
Worksheet 2: a tracking tool for select procedures from Worksheet 1 that have revisions
Current process:
In Worksheet 1, inserting "Yes" in column "X" triggers 6 cells from that row to be copied and pasted into Worksheet 2 into the next available blank row, as follows:
Column V copied into Column A
Column W copied into Column B
Column A copied into Column C
Column B copied into Column D
Column L copied into Column E
Column M copied into Column F
There are instances when the same row needs to be copied over to Worksheet 2 a second, third and fourth time (as there can be multiple revisions to the same procedure during the 2-month production cycle), which requires the user to go back to Worksheet 1 and remove and re-insert "Yes" in Column "X" each time.
I am looking for a more sophisticated solution where:
Users don't have to go back and forth between 2 spreadsheets, but can work exclusively in Worksheet 2
Users have a means from within Worksheet 2 to find and select rows from Worksheet 1, based on selecting the values for up to 3 columns in Worksheet 1 (Column A, B and D)
That from the selected rows, only specific cells are copied & pasted (as "values") into Worksheet 2 (as originally outlined above).
Thanks!
Andrea
EE-SampleExistingProcess.xlsm
ASKER
Hi Flyster,
While some of the rows may be copied over more than once over the span of the 2-month production period, they would only ever be transferred once each time they are transferred. The catalyst to transfer a row is when there are subsequent revisions to a procedure, and that it unpredictable, and can happen at any time during the cycle.
So unfortunately your code isn't something we can use for our situation. But thanks anyway!
Andrea
While some of the rows may be copied over more than once over the span of the 2-month production period, they would only ever be transferred once each time they are transferred. The catalyst to transfer a row is when there are subsequent revisions to a procedure, and that it unpredictable, and can happen at any time during the cycle.
So unfortunately your code isn't something we can use for our situation. But thanks anyway!
Andrea
I assume Flyster would be able to amend his code such that each run of the revision would check how many revisions have already occurred for a particular ID and then only repeat it if the number of revisions is less than the number quoted against it.
Thanks
Rob H
Thanks
Rob H
ASKER
If Flyster would be able to amend the code as you describe, Rob, then that would certainly be an improvement to my current process, and hopefully could be incorporated into a final solution that addresses the issues identified in my original post.
Hi Andrea,
Sorry I misunderstood your need. How do you determine if there has been a revision and which field do you use for your ID?
Paul
Sorry I misunderstood your need. How do you determine if there has been a revision and which field do you use for your ID?
Paul
ASKER
My apologies for missing this most recent post to my question!
The revision request is received by e-mail, but there is no ID to link the e-mail request to the spreadsheet. The user interprets the e-mailed request to identify which procedure is being revised, goes into Worksheet 1 in the spreadsheet, changes the value in the "Revision" column to "Yes", which then triggers the cells to autopopulate in Worksheet 2.
My goal is to be able to have a userform interface of some sort if possible, which would display the rows contained in Worksheet 1 with the ability for the user to check off the procedure(s) to be transferred into Worksheet 2, then click OK. This would trigger the rows (partial, as per my spreadsheet example supplied in my first post) to autopopulate in Worksheet 2, as well to as "clear" the userform of all checkmarks, so that the userform can be repeated as many times as required for the same or different procedures.
I don't know if my request is realistic or not. If not, then alternatively, if it is possible that, instead of "Yes" in the Revision column of Worksheet 1, if the user could use the number 1 for the first revision, and if a 2nd request for a revision came in for the same procedure, the user changes the number 1 in that row to 2, which would trigger the partial row to be copied over once more, for a total of 2 rows of the same procedure in Worksheet 2. There could be as many as 10 revisions on the same procedure. If a revision was cancelled, however, the user would not being revising the number downwards in the Revision column of Worksheet 1: they would be going into Worksheet 2 and manually removing the last row copied over for that procedure. So there is no requirement that the number of rows in Worksheet 2 match the number in the Revisions column in Worksheet 1. It is just about improving a currently clumsy, counter-intuitive interface, where the user toggles back and forth, inserting Yes, then removing it again at a later date and re-inserting it every time the same procedure needs to be copied over again.
The revision request is received by e-mail, but there is no ID to link the e-mail request to the spreadsheet. The user interprets the e-mailed request to identify which procedure is being revised, goes into Worksheet 1 in the spreadsheet, changes the value in the "Revision" column to "Yes", which then triggers the cells to autopopulate in Worksheet 2.
My goal is to be able to have a userform interface of some sort if possible, which would display the rows contained in Worksheet 1 with the ability for the user to check off the procedure(s) to be transferred into Worksheet 2, then click OK. This would trigger the rows (partial, as per my spreadsheet example supplied in my first post) to autopopulate in Worksheet 2, as well to as "clear" the userform of all checkmarks, so that the userform can be repeated as many times as required for the same or different procedures.
I don't know if my request is realistic or not. If not, then alternatively, if it is possible that, instead of "Yes" in the Revision column of Worksheet 1, if the user could use the number 1 for the first revision, and if a 2nd request for a revision came in for the same procedure, the user changes the number 1 in that row to 2, which would trigger the partial row to be copied over once more, for a total of 2 rows of the same procedure in Worksheet 2. There could be as many as 10 revisions on the same procedure. If a revision was cancelled, however, the user would not being revising the number downwards in the Revision column of Worksheet 1: they would be going into Worksheet 2 and manually removing the last row copied over for that procedure. So there is no requirement that the number of rows in Worksheet 2 match the number in the Revisions column in Worksheet 1. It is just about improving a currently clumsy, counter-intuitive interface, where the user toggles back and forth, inserting Yes, then removing it again at a later date and re-inserting it every time the same procedure needs to be copied over again.
For your review. I put a command button on worksheet 2 to open the user form. There's 10 rows on the form so you should see all data in worksheet 1 from A2 to D11. The user will select the checkbox next to the data needed to be copied onto worksheet 2. Selecting the copy command button will copy the required data to worksheet 2 and then clear the checkboxes. As I don't believe in reinventing the wheel, I used your original code for that. Hope this is what you were looking for!
EE-SampleExistingProcess.xlsm
EE-SampleExistingProcess.xlsm
ASKER
This is the kind of interface I was looking for...I'm so pleased!
I haven't used userforms before, and have a question before I figure out how to implement what you've achieved — our Worksheet 1 has about 2500 rows in it, so will the userform automatically add a scrollbar when the # of records exceed what the userform can show? I like the size of the userform you have created.
Please don't hesitate to let me know if this question should be entered in EE as a new one, rather than asked here...
Andrea
I haven't used userforms before, and have a question before I figure out how to implement what you've achieved — our Worksheet 1 has about 2500 rows in it, so will the userform automatically add a scrollbar when the # of records exceed what the userform can show? I like the size of the userform you have created.
Please don't hesitate to let me know if this question should be entered in EE as a new one, rather than asked here...
Andrea
I have to admit, I'm somewhat new to user forms myself. There's no automatic scroll bar, but I believe it can be achieved through programing. I have already figured out how to scroll down through the data, just having a little problem going the other way. Then I have to figure out how to transfer that information to the worksheet 2 update routine!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Super...thanks so much for all your work! And I appreciate the ability to page up and down with the form open. Look forward to implementing this.
Thanks! Hope it works out for you.
Flyster
EE-SampleExistingProcess.xlsm