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.

Quote from Carim

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 ...

Display More

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.

Quote from Carim

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 ...

Display More

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

Quote from Carim

Re,

Attached is your Test file as a starting point ...

Your input in Column H ( Budget Item) triggers the copy process ...

Hope this will help

Display More

This helps a lot! Thank you so much!

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?

Quote from Carim

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:

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.

Quote from Carim

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

Display More

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?

Quote from Carim

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.