Solved: consistant data in different table same workbook

[keilah]

Hi Experts (see attached workbook)

Looking at worksheet Deal Selection column(s) B and I

Range B9:B58 and I9:I58

If the user adds a new supply customer in column B then I would like this to be

Reflected in worksheet Tables column J5 onwards.

And visa versa for column I  add to column L5 onwards Tables worksheet

Need this to keep the defined name range table compact and do not wan to see

Empty spaces when I do my data validation later on

[lucas]

Hi Keilah,
If you found a solution, and I assume it was something like a dynamic range, why not post your solution for others instead of just marking it solved and moving on.......others will search the forum and maybe look at this thread for the same problem but there is no solution although it is marked solved.......

[keilah]

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.CodeName = "Sheet1" Or Sh.CodeName = "Sheet2" Then
If Sh.CodeName = "Sheet1" Then
Application.EnableEvents = False
If Not Intersect(Target(1, 1), Range("B9:B58,I9:I58")) Is Nothing Then
Range("B9:B58").Copy Sheet2.Range("J5")
Range("I9:I58").Copy Sheet2.Range("L5")
End If
Else
If Not Intersect(Target(1, 1), Range("J5:J18,L5:L18")) Then
Range("B9:B58").Copy Sheet1.Range("B9")
Range("I9:I58").Copy Sheet1.Range("I9")
End If
End If
End If
Application.EnableEvents = True
End Sub

here was my solution, that i though was working but apparently NOT....and also sorry for the fact that i forgot to mention that i had posted the question on experts exchange.....

i have also post this problem to be solved on experts,,,,,BUT not answers so far...