Forum Discussion

jobelledimaano's avatar
jobelledimaano
Copper Contributor
Aug 04, 2020

Automate data entry from another workbook

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.

excel
Formulas and Functions
Macros and VBA

2 Replies

  • TheAntony's avatar
    TheAntony
    Iron Contributor
    Aug 05, 2020

    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. 

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Aug 04, 2020

    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)

Resources