Copying from one sheet to another via input box

ammdumas

Active Member
Joined
Mar 14, 2002
Messages
469
I have a spreadsheet where there is a summary of data on one sheet and a form on another. What I'm trying to do is write a macros that will find a specific value in a column then copy the data in that row to fields on the form in the other sheet

For example:
-User inputs the transmittal number which is in Column A
-System finds correct number and then copies cells from that row into specific fields on the form on the other sheet

I got as far as creating the input box and I have a separate macros that copies data but it is very messy. Hint to myself: The fields in the form would always be pointing to the same column its just the row that would change. I figured the easiest way is to modify the function on the form to point to different rows as required...but I could be wrong. I need help. :ROFLMAO:
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

paliman

Active Member
Joined
Jul 7, 2002
Messages
254
If it's not a must writing a macro, you could use any cell to input the transmittal number and VLOOKUPs to fill your form fields.

If you are already in the middle of a macro, consider copying the inputbox value to any cell and use it as the first argument of the VOOKUPs
 

paliman

Active Member
Joined
Jul 7, 2002
Messages
254
If it's not a must writing a macro, you could use any cell to input the transmittal number and VLOOKUPs to fill your form fields.

If you are already in the middle of a macro, consider copying the inputbox value to any cell and use it as the first argument of the VOOKUPs
 

paliman

Active Member
Joined
Jul 7, 2002
Messages
254
If it's not a must writing a macro, you could use any cell to input the transmittal number and VLOOKUPs to fill your form fields.

If you are already in the middle of a macro, consider copying the inputbox value to any cell and use it as the first argument of the VOOKUPs
 

ammdumas

Active Member
Joined
Mar 14, 2002
Messages
469

ADVERTISEMENT

I see where you are going with this. But how do I change the table_array selection in vlookup automatically in each field? The idea is that the user just picks the correct number (say in 1501-123-1 in A24), and all fields on the forms are then updated to look at that row. This way still sounds like it needs to be changed manually.

Also, how would the macros fit the chosen variable into the vlookup formula? All it lets me do is pick the cell (i.e. A24) as opposed to typing in the actual value (i.e. 1501-123-1)? Help says I can look up text...

I'm getting close... :oops:
 

paliman

Active Member
Joined
Jul 7, 2002
Messages
254
Write the VLOOKUP like this:

=VLOOKUP(A1,Data_Source_Range,Any_Column,0)

and put the value to be looked for in cell A1. Also, yoy can use DATA-VALIDATION-LIST to create a drop down menu in A1.

About the macro, just copy the value of your inputbox in cell A1 and proceed like in the previous example:

MyAnswer = InputBox("Enter your number: ")
Range("D5").Value = MyAnswer

I hope this is clear enough.
And I hope it is useful too

Regards from the south
 

ammdumas

Active Member
Joined
Mar 14, 2002
Messages
469
Thanks Paliman> I got what you were saying and that way works much better (no code required). Never really used VLOOKUP before. Pretty handy...

FYI, this is what I eneded up doing...
1) List Validation in cell C3 for all of column A in sheet1

2) Any Field on sheet2 reads ...

=VLOOKUP('Sheet1'!C3,'Sheet1'!A:N,#,FALSE)

Where # represents whatever column has the data for that field...

Thanks again. :pray:
 

Watch MrExcel Video

Forum statistics

Threads
1,126,940
Messages
5,621,733
Members
415,853
Latest member
Newlife72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top