Macro for pulling cell data from one worksheet onto another

[bryan nelson]

I need some help with a macro to use on a worksheet that would be able to search and grab cell data from another work sheet and enter it in the main worksheet.

I have 3 worksheets: Input Log, Dispense Log, and Formulary Table. The Formulary Table contains a list of UPC's (Column A) and the corresponding Product Name (Column B), Brand (Column C), and Package Size (Column D).

What I am looking for is an ability to hotkey a window under the Input Log worksheet that would prompt for the UPC, a quantity, and the employees Initials and then would enter those fields in the respective columns in the Input Log worksheet. [UPC in Column A, Quantity in Column E, and Initials in Column G]. I then need the macro to take the UPC entered in Column A in the Input worksheet and search the Formulary Table for a match. Then take the data from Columns B, C, and D in that same row and populate the cells in Columns B, C, and D in the corresponding UPC row in the Input worksheet.

Is there a way to have the data automatically be entered in the next open row below the current data set? I'm not very good at writing Macros in Excel so any help would greatly be appreciated! Thanks!

[simarui]

There is absolutely a way, but I find it fairly difficult to write VBA from scratch without some sample data to test on. do you already have your userform working? and the hotkeys assigned? Can you upload a sample workbook?

general pointers for your last question - it's fairly common to declare a long variable which you can set to the last used row with some code like this:

Please Login or Register  to view this content.

then you can copy values to .Range("A" & lr + 1) to print in the next open row.

remember you'll need to redefine lr at this point (if you're running a loop you can do so at the start of the loop or at the end just do lr = lr + 1)


**Additional info**
As for matching your UPC code, i've been using the find() method quite a bit lately - typically in some sort of a loop along these lines:

Please Login or Register  to view this content.

(untested)

that could probably be cleaner if you declared a few more variables or resized the ranges or something

[bryan nelson]

C-II Log Test.xlsm

This is the workbook I am currently working with. I went with a userform command button instead of a hotkey. Still having some trouble figuring out how to pull the cell data from Sheet4 onto Sheet 2 or Sheet 3. Does this provide any clarity?

Thanks again.

[simarui]

looks like this is working

Please Login or Register  to view this content.

as attached

[bryan nelson]

Thanks simarui! That works perfect

[simarui]

glad i could help and thanks for the rep! don't forget to mark the post solved