VBA code to automatically populate number of tables according to headers.

  • We are devising a database, to record and analyse sales through different areas, zones and departments.
    As it is essentially an analysis database, (plus other reasons), Access is not suitable, but part of it does rely on sales figures being input across many different spreadsheets.

    For ease of input and accuracy, we have devised a userform.
    The user selects the area and timeframe and then inputs figures within textboxes for each department within the different zones on a multipage userform.

    The query is, we would want the info as input to automatically populate the different zones within the different tables within the different department spreadsheets corresponding to the area and timeframe selected, when the submit button is clicked

    The timeframes mirror the table headers. The tables have been named on each spreadsheet as AreaXDepartment, for example area1hardware, area1groceries etc etc. The zones are entered as rows.

    In addition, if a user selects a timeframe and area for which the data is already on the table and clicks submit, a warning box will appear, “This information is already entered. Do you wish to overwrite?” with an option to cancel, (to bring back to the userform) or proceed, (to overwrite the info on the tables.
    Obviously some kind of lookup is required but I can’t get it working especially when tables are involved!

    A sample of the database has been attached and if anyone could point me in the right direction, I would be extremely grateful.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!