Solved

Userform (?) for selecting specific row(s) from one spreadsheet and paste partial part of row into another spreadsheet

Posted on 2013-11-10
12
366 Views
Last Modified: 2013-11-26
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:
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
0
Comment
Question by:Andreamary
  • 6
  • 5
12 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 39637358
I made a slight revision to your code. Instead of entering "Yes" for revision, you enter the number of revisions you want to copy over.

Flyster
EE-SampleExistingProcess.xlsm
0
 

Author Comment

by:Andreamary
ID: 39638586
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
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39638644
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
0
 

Author Comment

by:Andreamary
ID: 39638795
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.
0
 
LVL 22

Expert Comment

by:Flyster
ID: 39640019
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
0
 

Author Comment

by:Andreamary
ID: 39664293
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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 22

Expert Comment

by:Flyster
ID: 39667962
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
0
 

Author Comment

by:Andreamary
ID: 39673341
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
0
 
LVL 22

Expert Comment

by:Flyster
ID: 39675758
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!
0
 
LVL 22

Accepted Solution

by:
Flyster earned 500 total points
ID: 39676694
OK. The scroll down and up buttons will scroll through your data. Selecting a checkbox and copy revision will add your data to worksheet 2. As you can't page up or down while the user form is open, I added two buttons on the left (U & D) which will do it without having to close the form. Let me know what you think!
EE-SampleExistingProcessScroll.xlsm
0
 

Author Closing Comment

by:Andreamary
ID: 39679567
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.
0
 
LVL 22

Expert Comment

by:Flyster
ID: 39679674
Thanks! Hope it works out for you.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now