User Form to search and return data over multiple cells and worksheets

**nathandavies9** · 03-20-2017, 06:43 AM

Hi All,
I was wondering if any one could help with a some coding that i'm trying to find.

I have created a userform which contains a textbox, which i will be copying multiple rows into from another workbook. i was wondering if anyone has ever created some code to the copy the information into a worksheet which is selected based on a combobox and the return all the information based on the column in the worksheet.

When you look at the workbook, the userform is on selection of the parts list tab on the front sheet, i then want to copy all the information into column C on worksheet "P" for example, and then all the other information is added to the other cells.

I have attached my excel sheet for your assistance, the

**sintek** · 03-20-2017, 09:19 AM

Hey Nathan

This will get you started:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

**nathandavies9** · 03-20-2017, 09:35 AM

Thanks, i will check this out see what i can do with it.

Is it possible to have multiple lines on a text box at the minute i can only get 1 line, and i want to copy around 50 lines in at the same time.

**sintek** · 03-20-2017, 09:46 AM

https://support.office.com/en-us/art...9-4b8a55b7cb03

**nathandavies9** · 03-20-2017, 09:51 AM

Got that thanks!

**sintek** · 03-20-2017, 09:54 AM

For interest sake, why would you want to copy 50 lines into a textbox.

**nathandavies9** · 03-20-2017, 09:56 AM

At the minute i have 2 worksheets, and that is populated automatically from a different program, i'm trying to find a quick and easy way to copy all the information into my worksheet and then create a vlookup for all my information.

**nathandavies9** · 03-20-2017, 09:59 AM

so my long term goal is to be able to copy the information into a worksheet "power" for instance in a set column and then do a search for all the other information that is located across different worksheets.

Capture.JPG

it will be a bit like the attached

**nathandavies9** · 03-20-2017, 10:02 AM

it would then be inserted in to the worksheet on the parts list column like so.

Capture1.JPG

**sintek** · 03-20-2017, 10:02 AM

Over my head...whoosh. Will have to load sample files and detail required result.

**sintek** · 03-20-2017, 10:05 AM

So, you want to import the textbox info into one column each wit it's own row and then have a vlookup formula in the other columns to get required information.

**nathandavies9** · 03-20-2017, 10:06 AM

This is my workbook, and everything i have completed so far.

**nathandavies9** · 03-20-2017, 10:07 AM

Yes, it will have to search the full workbook and then find the relevant information.

**sintek** · 03-20-2017, 10:09 AM

Think you gonna have to use a multiselect-listbox instead of textbox.

**nathandavies9** · 03-20-2017, 10:12 AM

would i still be able to copy from another worksheet?

**sintek** · 03-20-2017, 10:15 AM

Check out this linkhttps://www.mrexcel.com/forum/excel-...e-rows-ws.html

**nathandavies9** · 03-20-2017, 10:29 AM

This is more for inputting the information and then inserting into a ws, i have extracted the insert but i'm looking to completely automate it, from the copy and paste.

**sintek** · 03-20-2017, 10:47 AM

http://www.ozgrid.com/forum/showthread.php?t=56358
https://www.mrexcel.com/forum/excel-...e-rows-ws.html

**nathandavies9** · 03-20-2017, 11:16 AM

Thanks Sintek, i will look at these see what i can come up with.

**sintek** · 03-20-2017, 11:26 AM

Hey there Nathan

i suggest using a listbox and then run code to fill the selected sheet

Something like this:

Please Login or Register  to view this content.

**nathandavies9** · 03-20-2017, 11:29 AM

I'm not sure where to put this in the code? i'm just getting started with code so i'm struggling at the minute.

**sintek** · 03-20-2017, 11:39 AM

Hi Nathan

Has been a lot of back and forth going on but this is unfortunately the game - not knowing what one's exact requirements are. I have attached a sample workbook which auto populates a listbox from an array and then when add is pressed, it puts the items in a chosen sheet below each other. Play around with this as I am certain that this is what you require.

**nathandavies9** · 03-20-2017, 12:01 PM

Hi Sintek,
Thanks for your help on this, I have looked at your code and i think its very close to what i require but rather than having a list box i require a text box because i will be inputting the information myself.

I will have two documents open at the same time "Sample_BOM" and "Heat Calculation", what i will be doing is copying the information from Column C "part Number" of the Sample_BOM into the text box one userform1, then i would press the add button on userform1 which would then input that information into which ever worksheet i selected so in this case it would be ws:POWER.

I have attached both sheets for your information.

**sintek** · 03-20-2017, 12:54 PM

Nathan, Do me a favour

Open the two attached workbooks and run the code.
This works as per your explanation in Thread#23, only difference is that the listbox is autopopulated by The Sample_BOM worksheet - No need to copy and paste.

Choose "POWER" From ComboBox1

**nathandavies9** · 03-20-2017, 12:57 PM

sintek, that works beautifully!!

I know have one more little request, is it possible to put the next column in as well. Column D?

**sintek** · 03-20-2017, 01:19 PM

Put this code in the UserForm_Initialize event. This will populate listbox with Column c and d.
Still trying to figure out the other part lol......Still learning as well.

Please Login or Register  to view this content.

**sintek** · 03-20-2017, 01:45 PM

This code pastes the listbox values to corresponding sheet Columns C & D

Please Login or Register  to view this content.

**nathandavies9** · 03-20-2017, 01:46 PM

sorry! just seen the next response. that works perfectly, i just need to find out how to complete a vlookup based on Column C once its pasted into the column.

**nathandavies9** · 03-20-2017, 02:20 PM

I have been looking at how to populate the remaining cells and i think the best way will be INDEX AND MATCH, I'm trying to work it out now but not sure how to get it to work across every worksheet automatically.

**sintek** · 03-20-2017, 02:43 PM

Where must it take it's value from. Upload a sample with results highlighted

**nathandavies9** · 03-20-2017, 02:50 PM

I have highlighted the cells which will require populating and the worksheets which they should be populated from (also highlighted on there)

attached is a copy.

**nathandavies9** · 03-20-2017, 02:50 PM

it will not be limited to these 2 worksheets, there are around 16 worksheets on the final document.

**sintek** · 03-20-2017, 02:51 PM

Here is a few excellent starting links
http://ccm.net/forum/affich-255685-l...muliple-sheets
https://www.mrexcel.com/forum/excel-...-workbook.html

**sintek** · 03-20-2017, 02:53 PM

Going to have a problem as the part no's don't match i.e.
5SY61047 vs 5SY6104-7 etc

**nathandavies9** · 03-20-2017, 02:57 PM

I will be double changing all the part numbers so that they match just not got round to it yet.

i have changed them on this example sheet

**sintek** · 03-20-2017, 03:08 PM

Once you sort that out, copy this formula into Column E and paste down invoking with CONTROL+SHIFT+ENTER

Formula:

Please Login or Register  to view this content.

**sintek** · 03-20-2017, 03:09 PM

For the next column, change this part of the formula

Formula:

Please Login or Register  to view this content.

to

Formula:

Please Login or Register  to view this content.

and so on

**sintek** · 03-20-2017, 03:15 PM

Forgot to mention. Somewhere on a open sheet, write down all the sheet tab names and name this range "MySheets"

**nathandavies9** · 03-20-2017, 03:17 PM

Its got an error, #Name? doesn't seem to work

**nathandavies9** · 03-20-2017, 03:21 PM

Got an different error now = #Value?

**nathandavies9** · 03-20-2017, 03:24 PM

Fixed the error.

Will complete some testing tonight but thing that is now working!

**sintek** · 03-20-2017, 03:34 PM

Nathan, herewith attached a sample sheet with results highlighted. MySheet range is Sheet1 Range B1:B2
You should be able to sort out the rest

**sintek** · 03-20-2017, 03:35 PM

Remember to mark as solved and add Rep Points

**nathandavies9** · 03-20-2017, 03:39 PM

SINTEK!

You have been a massive help today and i think i have got everything i need now! i will defiantly add rep and mark as solved

cheers
ND

**sintek** · 03-20-2017, 03:53 PM

Only a pleasure...This Forum is the SH..!!!
Excellent advice and assistance 24/7
I would be lost without it

User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Re: User Form to search and return data over multiple cells and worksheets

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Re: Please help for Excel User form send data for multiple worksheets

[SOLVED] User Form to execute search and return all values to the user form for editing

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions