• 0

Solved Auto Insert New Row On Different Worksheet

  • 0

I have a spreadsheet with 10 worksheets on it, 1 for each supplier with the same standard layout on each:
Column A: Product Code
Column B: Product Name
Column C: Cost Price
Column D: Sales Prices

I use these individual spreadsheets to import my products to sage. It would make life a lot easier if I had 1 worksheet in this same file with all the information from the others so that I would only have to import 1 file e.g. every time i add a row of information to either one of those 10 supplier sheets it adds a new row with the same information into the master sheet. How would i do this?


1 Answer

  1. Let’s get the code into your workbook, then deal with the options for running it.

    First, I am assuming that you are using Excel 2007 or later on a Windows based machine. If you are using an earlier version or using a Mac, these steps may not work exactly as written. Let me know.

    Second, I suggest that you do this in a backup copy of your workbook. Macros cannot be undone and I’d hate to have you run the code and then save your original book before you know if the code does what you want it to do.

    1 – Copy the code from Response #4 to store it on the clipboard. Your copy should start with Sub BuildImportSheet() and end with End Sub, inclusive
    2 – In your workbook, press Alt-F11 to open the VBA editor
    3 – Click the Insert tab and choose Module
    4 – Paste the code into the pane that opens. You should not see any red text. If you do, you copied more (or less) than the actual code.
    5 – Close the VBA editor
    6 – Save the workbook. Since it now contains a macro, it must be saved as an xlsm or xlsb file. xlsm will be fine.

    OK, now that the macro is in the file, you have a number of options for running it. I’ll offer 3 options, although there are many more. A Google search on Macro buttons will turn up a lot of info. In my opinion, Option 2 is probably the best of the 3.

    Option 1:

    The most brute force, basic method is to open the VBA editor via Alt-F11, open the module with the code, place the cursor anywhere inside the code and click the green Run triangle in the top ribbon. You probably won’t even realize that anything happened, but the last sheet in the workbook should contain all the data from the other sheets.

    In most cases, you (or your users) are not going to want to open the VBA editor every time you want to run the code, so a better idea is to create a button in the workbook so the code can be run with a click. Options 2 and 3 are different ways to create a macro button.

    Option 2:

    Place a button on your Quick Access Toolbar. (QAT)

    1 – Right click the QAT and choose Customize Quick Access Toolbar
    2 – Click the drop down for “Choose Commands From” and choose Macros
    3 – BuildImportSheet should appear in the pane below the drop down
    4 – Select BuildImportSheet, click Add to add it to the right hand pane
    5 – Click the drop down for Customize Quick Access toolbar and choose the name of your workbook. (This done so that the button does not show up in every workbook you open)
    6 – If you want, you can click the Modify button below the right hand pane and choose a different shape for the button. I have about 20 macro buttons that I use all the time, so I try to choose a shape that reminds me of what each macro does.
    7 – Click OK to close the Customize dialog box.
    8 – Save the workbook

    In the QAT, you should see the new button. Hover over it and the macro name should appear. Click it and the code should run. Whenever you open the workbook, the button should be there.

    (In case you didn’t know it, you can add all sorts of buttons to the QAT to make Excel easier to use. Not just macro buttons, but buttons for just about every Excel feature that can be found hidden behind the various ribbons.)

    Option 3:

    1 – On any worksheet, click the Insert tab and choose any shape you’d like
    2 – Place the shape on the worksheet. You can add text to the shape if you want to
    3 – Right click the shape and choose “Assign Macro”
    4 – In the dialog box that opens, you should see the BuildImportSheet macro
    5 – Double click the macro name and the dialog box should close
    6 – When you hover over the shape, the cursor should become a finger, telling you that it is a “link”. If you click the button, the macro will run

    You can add a shape button on every sheet if you want to. However, if you are going to add a shape button to every sheet, you might as well just add a single button to the QAT.

    Let me know if this makes sense and if you need any further help.

    Click Here Before Posting Data or VBA Code —> How To Post Data or Code.

    • 0