Copy/Update Table Data to another Table via VBA

  • Hello!


    I have a dynamic table in sheet 1 (Budget) where specific cells must be copied to a new row in another dynamic table in sheet 2 (COA), so that:


    Entry in Category Title in table 1 is copied to Account Title in table 2,

    Budget Code to Budget Code,

    Item Code to Sub Code, and

    Budget Item to Sub Account.


    A command button (Create Expense A/C) should appear in each new row added in table 1 via the existing Add Budget Item command table, which if clicked will trigger adding another row in table 2 and the copying process.


    I'm fairly new to VBA using Excel for Mac 2019 and so I am not sure how to make this happen. I'm attaching the file for reference.


    Hoping someone could help me on this, thanks!

  • A new row may be automatically added in table 1 if the above row is populated (instead of clicking Add Budget Item) but I also don't know the appropriate code.

  • Hello,


    Currently both your macros both expand the size of your tables ...


    Can you explain how you are planning to use your General Ledger ...


    And specifically what data needs to be copied from which Source to which Destination ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hi Carim! I'm creating a Film Accounting file for my production company.


    I want to copy from Budget table to COA table he following:

    Category Title > Account Title

    Budget Code > Budget Code

    Item Code > Sub Code and

    Budget Item > Sub Account

  • So that every time I create a new row of data in the Budget table, a command button appears which allows me to copy it directly to the COA table.

  • Not sure to fully understand your process ... and the need for a Command Button for each newly added row ...


    A couple of remarks :


    1. If your Table 'COA' is only a copy of the 'Budget Table' ... an equal sign between the two tables would do the job


    2. If your Table 'COA' is NOT a replicate of the 'Budget Table' ... then ...as you input the Budget Item in Column H, an event could handle the copy process ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • I think no. 2 applies in my case. COA is not a replicate of Budget table, since Budget table only creates Expense accounts.


    In COA table, I need to create Asset, Liability, and Equity accounts, in addition to the Expense accounts I will create by copying those data already in the Budget table.


    I'm not really sure how to do the exact copying process

  • This helps a lot! Thank you so much!

    Thanks for your Thanks AND for the Like :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Just a follow up: is it possible to reflect any changes in the 'Budget' table data row reflect in the 'COA' table data row?


    So that when I change, for example, an existing 'Budget Item' data in 'Budget', the change is reflected in the corresponding 'Sub Account' data in 'COA'.


    Like an "Edit and Save" button, maybe?

  • Hello,


    What you are requesting is indeed feasible ... as long as there is a Unique Identifier


    Why don't you create an example in your test file before attaching it ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hello,


    What you are requesting is indeed feasible ... as long as there is a Unique Identifier


    Why don't you create an example in your test file before attaching it ...

    Here's the file with sample data entries in both tables.


    I've started adding another worksheets, but they are not yet done.


    Also, I've tried sorting both tables automatically based on the "Budget Ref./Account Code" columns once a new row of data is completely entered.


    This is a sample VBA Code I found on the internet:

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("J:J")) Is Nothing Then
    Range("J4").Sort Key1:=Range("J5"), _
    Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom
    End If
    End Sub


    However it doesn't seem to work. :(

  • Hello,


    You are tackling too many topics at the same time ...


    It is much safer to go step by step ...


    Regarding the Edit / Save question ... attached is Version 2


    If you are modifying a Budget Item in Column G ... you only need to double-click on the new choice ... and it will replace the old one in the Chart of Accounts worksheet ...


    Hope this will help

  • Once the Edit / Save issue is fixed ... could you explain in detail the Automatic Sorting you need ...


    When you say : " once a new row of data is completely entered " ...


    Could you be more precise :


    In each Sheet, which column exactly is the ' last one ' ... where you are manually adding data ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Thanks again!


    However, all 4 columns and not just 'Budget Item' should be updated.


    I'm also thinking whether a command button is more apt to trigger Edit and Save than just double clicking. What do you think?

  • Hello,


    For the 4 columns, below is the modified version


    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Thanks again!


    However, all 4 columns and not just 'Budget Item' should be updated.


    I'm also thinking whether a command button is more apt to trigger Edit and Save than just double clicking. What do you think?


    Regarding one Command Button to trigger Edit and Save :


    How would you plan to correctly indicate which row from the Source sheet needs to be updating which row from the Destination sheet ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Regarding one Command Button to trigger Edit and Save :


    How would you plan to correctly indicate which row from the Source sheet needs to be updating which row from the Destination sheet ?

    I guess that would be a problem, since in the latest version that I edited (see V3 below) I intend to identify each row with Budget Ref. = Account Code. But the Budget Ref. changes right away once I edit it.


    Double click is possible, I guess, but maybe not limit it to Budget Item, I guess? Allow Edit and Save in Unit Code, Category Title and Item Code as well?

  • Re: Sorting


    I want to sort the table based on the "Budget Ref." column in "Budget" table and "Account Code" column in "COA" in an ascending order.

Participate now!

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