How to write a macro to copy 2 excel tables to be pasted to another excel worksheet??

Please help!!
I have two excel tables:
- one excel table with 50 Account Names (in worksheet 1)
- one excel table with 1500 Codes (in worksheet 2)

How do I write a macro to copy the two tables to a 3rd worksheet?
50 Account Names (in worksheet 1) should be tagged to 1 Code (in worksheet 2)==> to be pasted on Worksheet 3.

Please refer to the enclosed file for more details.
The details/ tables have been simplified for illustration purposes.

Table 1
Accounts Name Accounts
Cereals 1000
Milk 2000
Chocolates 3000
Butter 4000

Table 2
Region Country State BU Code
WW US Texas HDD1 123456
WW US Texas HDD1 123456
WW US Texas HDD1 123456
WW US Texas HDD1 123456

Hi Patel,
Awesome!! Many thanks for your sharing!!
Still the Macro need some fine-tuning...

Technical hiccups as follows:
1. The ranges that I need to copy are different in the active worksheets. (NOT sh.Range("A1:G1")
--> How do I change this?? 2 worksheets have 2 different ranges.

2. I need each line (each code) in 'worksheet 2' to be repetitive (in actual fact repeat 50 lines!)for the Account line we could find in 'worksheet 1'
--> Could we edit the macro to have these lines repeated in 'RDBMergeSheet' ??

'RDBMergeSheet' shows how the report looks like after I generate the macro.
Please refer to 'worksheet 3' on how the final outcome should look like.
Please help me to close these gaps.


By the way, I have removed:
1. 'Find the last row with data on the DestSh
Last = LastRow(DestSh)
-->As this is causing some macro errors


2. 'Optional: This will copy the sheet name in the H column
DestSh.Cells(Last + 1, "H").Resize(CopyRng.Rows.Count).Value = sh.Name
-->As I do not need this in my RDBMergeSheet


THANK YOU!!!