Automate data entry from another workbook

%3CLINGO-SUB%20id%3D%22lingo-sub-1565084%22%20slang%3D%22en-US%22%3EAutomate%20data%20entry%20from%20another%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1565084%22%20slang%3D%22en-US%22%3E%3CP%3EPlease%20help%20me%2C%20i%20want%20to%20automate%20a%20task%20in%20excel%20but%20i%20don't%20know%20how%20to%20do%20it%20since%20data%20extraction%20is%20complicated.%20Here's%20what%20needs%20to%20be%20done%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20When%20i%20enter%20the%20JT%20number%20excel%20should%20be%20able%20to%20automatically%20collect%20the%20data%20of%20the%20following%20from%20another%20workbook.%3C%2FP%3E%3CP%3Ea.%20Part%20Number%3C%2FP%3E%3CP%3Eb%20JT%20Quantity%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eon%20enclosed%20picture%20the%20process%20details%20should%20be%20populated%20on%20the%20process%20column%20on%20the%20other%20excel%20workbook.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1565084%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1565155%22%20slang%3D%22de-DE%22%3ESubject%3A%20Automate%20data%20entry%20from%20another%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1565155%22%20slang%3D%22de-DE%22%3E%3CBR%20%2F%3ESmall%20and%20fast%20VBA%20solution%20from%20sheet%20to%20sheet%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20Sub%20commanbutton1_click()%20%3CBR%20%2F%3E%20Sheets(%22Table1%22).%20Range(%22B25%3AG500%22).%20Copy%20Sheets(%22Table2%22).%20Range(%22B2%22)%20%3CBR%20%2F%3E%20Sheets(%22Table3%22).%20Range(%22B25%3AG500%22).%20Copy%20Sheets(%22Table4%22).%20Range(%22B2%22)%20%3CBR%20%2F%3E%20'ect.%20%3CBR%20%2F%3E%20End%20Sub%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20Small%20and%20fast%20VBA%20solution%20from%20workbook%20to%20workbook%20%3CBR%20%2F%3E%20Sub%20test()%20%3CBR%20%2F%3E%20Workbooks(%22test1.xls%22).%20Worksheets(%22Sheet1%22).%20Cells(1%2C%201).%20Copy%20%3CBR%20%2F%3E%20Workbooks(%22test2.xls%22).%20Worksheets(%22Sheet1%22).%20Cells(5%2C%205).%20Paste%20%3CBR%20%2F%3E%20End%20Sub%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20*With%20both%20proposed%20solutions%2C%20the%20areas%20%2F%20cells%20%2F%20workbooks%20would%20have%20to%20be%20adapted%20to%20your%20requirements.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20Best%20regards%2C%20%3CBR%20%2F%3E%20nikolino%20%3CBR%20%2F%3E%20I%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1566522%22%20slang%3D%22en-US%22%3ERe%3A%20Automate%20data%20entry%20from%20another%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1566522%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F748865%22%20target%3D%22_blank%22%3E%40jobelledimaano%3C%2FA%3E%26nbsp%3B%2C%20if%20you%20need%20a%20non-macro%20solution%2C%20Power%20Query%20can%20do%20what%20you%20are%20looking%20for.%20You%20would%20load%20the%20details%20from%20your%20Master%20file%20into%20the%20working%20file%20using%20PQ%20and%20use%20it%20for%20lookup%20purposes.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Please help me, i want to automate a task in excel but i don't know how to do it since data extraction is complicated. Here's what needs to be done:

 

1. When i enter the JT number excel should be able to automatically collect the data of the following from another workbook.

a. Part Number

b JT Quantity

 

on enclosed picture the process details should be populated on the process column on the other excel workbook.

2 Replies

Small and fast VBA solution from sheet to sheet

Sub commanbutton1_click()
Sheets("Tabelle1").Range("B25:G500").Copy Sheets("Tabelle2").Range("B2")
Sheets("Tabelle3").Range("B25:G500").Copy Sheets("Tabelle4").Range("B2")
‘ect.
End Sub

Small and fast VBA solution from workbook to workbook
Sub test()
Workbooks("test1.xls").Worksheets("Sheet1").Cells(1, 1).Copy
Workbooks("test2.xls").Worksheets("Sheet1").Cells(5, 5).Paste
End Sub

*With both proposed solutions, the areas / cells / workbooks would have to be adapted to your requirements.

Best regards,
nikolino
I know I don't know anything (Socrates)

@jobelledimaano , if you need a non-macro solution, Power Query can do what you are looking for. You would load the details from your Master file into the working file using PQ and use it for lookup purposes.