Forum Discussion

iapetustitan's avatar
iapetustitan
Copper Contributor
Nov 13, 2020

Populating cell data from one workbook to another - checkboxes/buttons/VBA?

Hi there. Looking for some info on auto populating certain cells with certain data from one spreadsheet to another.
Two sheets attached. The 'Test_draft_1' is a first draft idea for a (large) stock/inventory sheet covering multiple areas. Just playing with ideas to see what works best for our needs, I'm still learning the ropes with excel so go easy.

Generally, everything works as needed (there are a lot more item types to populate so it will get even longer, sadly) but I'm trying to come up with a way to create a new order form for specific items that require a re-order. My thoughts were a checkbox system or similar where, once all selected, the choices could be 'confirmed' and the selected data copied to an order form (actual order form also attached - no scope for changing this except where I might be able to continually (and automatically) add new rows as needed).

 

Ideally, the price per item would also copy across (that data not in the Draft sheet right now) and, in a dream scenario, could even suggest quantity before 'confirming' data. 

I know I'm asking a lot from a spreadsheet but I need to come up with something while we wait to get confirmation for SysDevs from IT to help create something more bespoke (looking forward - QR scanners for staff to update movements, auto flagging of stock level thresholds and auto order form completion. Can't imagine excel could do that but that's what I'd prefer).

Any tips, pointers, suggestions on how I could do this sheet to sheet 'auto' transfer would be very appreciated.
Point of note: stuck on Excel 2013. No scope for more up to date version.
(removed all 'sensitive' data)
EDIT: removed posted files. 

 

Thanks

Malc

developer
excel
Formulas and Functions
Macros and VBA
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Nov 13, 2020

    iapetustitan 

    Here is a VBA example, untested.

    Sub data_walk ()
    Dim wbA As Workbook
    Dim wbB As Workbook
    Dim wbNeu As Workbook

    'Open the "B.xls" file if it is not already open
    Application.DisplayAlerts = False
    Workbooks.Open Filename: = "C: \ Code \ B.xls"
    Application.DisplayAlerts = True

    Set wbA = Workbooks ("A.xls")
    Set wbB = Workbooks ("B.xls")

    'create new workbook .... will later be "C.xls"
    Application.Workbooks.Add
    Set wbNeu = ActiveWorkbook

    'Example: cells A1 and A2 from folder "B.xls" are multiplied together
    'and the result is written into the new folder / cell A1
    wbNeu.Sheets (1) .Range ("A1") = wbB.Sheets (1) .Range ("A1") * wbB.Sheets (1) .Range ("A2")

    'The folder is saved as "C: \ Code \ C.xls"
    Application.DisplayAlerts = False
    wbNeu.SaveAs Filename: = "C: \ Code \ C.xls"
    Application.DisplayAlerts = True

    'Folder "B.xls" is closed (without saving)
    Application.DisplayAlerts = False
    Workbooks ("B.xls"). Close
    Application.DisplayAlerts = True
    End Sub

    For the example, the "B.xls" file should contain numbers in cells A1 and A2.

    The macro is started from file "A.xls" and opens file "C: \ Code \ B.xls \" (if not already open), cells A1 and A2 are multiplied from this file and the result in cell A1 in the new file "C: \ Code \ C.xls \".

     

    Fished from the internet ... don't ask me where, believe from some german site.

    Simply sent as an example, it may help you in your project.

     

     

    Hope I was able to help you.

     

    Nikolino

    I know I don't know anything (Socrates)

     

    * Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here

     

Resources