Save Data from Multiple Sheets to Another Sheet by Referring to Specific Value in Cell

    Hi,


    I really hope someone can help! I would like to save input data from 3 worksheets (Input1, Input2, Input3) to a another worksheet named DB in the same workbook. I have attached an example workbook for reference. RDW07.xlsm


    I have 3 requests to achieve this:


    1. If I copy over a specific Entry's data back to all the Input sheets (this button already works in the DB sheet) by clicking on a entry in the DB and then click the Load Entry Data button, and then make changes to the input data, then when you click the Save to Database button then it must save all data back to the DB sheet but on the same line with the same Entry ID# in the database sheet (DB) according to same Entry ID# in cell "C5" in Input1 sheet. So it is basically you just saving (over-writing) any changes made to that specific entry back to the DB sheet.


    2. When you click the "New Entry" button on Input1 sheet, then the code will get the next Entry ID# number in the sequence in column "A" of the DB sheet and place this number in cell "C5" on Input1 sheet, but it must not give a runtime error if there are no entries in the DB (clean DB), it should then begin from scratch with E0001 as the new Entry ID# number in the DB if there is none and put this number in cell "C5" in Input1 sheet.


    3. After the new Entry ID# has been created (in point 2 above) in cell "C5" in Input1 sheet, and all other data has been captured in all input sheets (Input1, Input2, Input3), Then I would like to save this new Entry and all it's data captured from these Input sheets by clicking the "Save to Database" button, which will then copy all input data from all the input sheets to the DB sheet to the next row of the table under the last row saved as per the column headings.


    Thank you for the help!:)


    The simplest way to increment the number is to use a number but custom formatted to begin with S then use the MAX Function on the Input sheet.

    1. Why have you got 3 input sheets? I would have a duplicate input sheet for amendments which would use VLOOKUP to populate the form based on the ID. Then use .FIND to locate the correct row to amend.

    2. I've converted the data to a Table so that the formula will be dynamic and you won't need to keep changing the range as data is added

    3. I've added some code to save to the database, it needs extending. It could be faster if your input cells were not separated by rows.

    Hi royUK,


    Thanks for the reply and the help.


    I will not be able to use the MAX function on Input1 sheet because every now and then I would have to copy data from the DB sheet to the input sheets to do updates and this would then over-write cell "C5" everytime and this would over-write the MAX formula inserted there.


    The reason for the 3 input sheets is because this is only an example workbook to share here to try to get solutions that I am having on my actual project workbook. This example workbook is just an extremely simplified and basic version of my actual workbook. My project workbook actually has 15 input sheets for different criteria and variables that need to input into the different input sheets. And because of this VLOOKUP function would not work either as the whole workbook was not designed to work in that way.


    To answer you point 3. I have attached a picture to give a bit of context to what I tried to say above and to show the design and layout of my project workbook and why my cells are separated by rows and columns.


    I have a set of other code that I will send shortly that I would like your opinion on that might be able to solve my problem but I don't know enough of VBA coding to make the necessary adjustments.?

    As mentioned in my last post I do have some code that I have tried and it does work to save data to from the multiple input sheets to the DB sheet, however I have the following questions with this code:


    1. Is it possible to adjust this code so that it can write a new entry to the next line in the table after the last line that was populated? At the moment this code overwrites the last line data and does not create a new line under the last line. For example entry entry S0010 has been populated in the table with all it data but then this code would over-write everything in line S0010 and put all S0011 (new entry) data here and not underneath S0010 line which is next in the sequence.


    2. Is it also possible to do adjustments to this code along with point 1. above so that if a old entry is copied from the DB to the entry sheets that when any changed have been made and you want to save these changes then it would over-write all old data on the specific line of that entry ID# in the DB sheet?


    3. In this code it uses a column range of "B50000" would probably need to change to .ListObjects(1).DataBodyRange I think to work with the last row of the table in DB?


    Here is the code that I used in my actual project workbook and not the example book shared here (could easily be adjust to make it work in example workbook), hope this helps:

    I suggested using an separate sheet for Amendments.


    15 Input sheets seems a lot to manage, it's very difficult to help without knowing the project.

    To get the next row change to this. Also, don't use a fixed range, use Rows.Count


    Your code also doesn'y use the With Statement correct, compare to the below line

    Code
    With Sheets("DB")
LRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row

    T o amend an entry you would need to find the correct row by using the .FIND method based on the ID.


    You would only need to use DataBodyRange if you are working with a range formatted as a Table

    Quote from royUK

    T o amend an entry you would need to find the correct row by using the .FIND method based on the ID.

    Would you be able to assist with this part? I tried to google/YouTube what to try next but I have no idea what to do .:/ I have attached the updated example workbook with the code you suggested for your reference.


    The only problem I need to sort out is to amend an entry and save the changes back to the DB sheet. I am also not sure should one create a separate module for this action? My thinking was that when you click the "save button then the code would check to see if the Entry# in Input1 sheet in cell "C5" exists in the DB sheet with the FIND function as you suggested, if it does not exist then it will run Module4 code (to add new entry to last line), but if it does exist then it would run another i.e. Module (Module5) code to update and overwrite that specific Entry# row? Or is there another way to achieve this?


    If you could help with this last code I would be super grateful!

Participate now!

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

Register Yourself Login