Copying from one sheet to another via input box

[ammdumas]

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.

 

[paliman]

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]

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]

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]

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...

 

[paliman]

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]

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. ray: