Solved: How to Link two sets of ranges

[IgnBan]

How can I can I mirror (link) two sets of ranges in two workbooks.

What I need is to link (like when you in a cell press the equals "=" sign in one cell and click a cell in another workbook and pres enter) two sets of ranges in two workbooks. As an example let use Workbook1 and workbook2. I need to link all the cells from workbook1 (A:C) and (F:X) to workbook2 same ranges. How can I accomplish this?

Thanks in advance for all the replays

[IgnBan]

OK, lets make it shorter (A:C1000) and (F:X1000)

[mdmackillop]

Will both books be open? Don't the ranges need to be the same size?

[IgnBan]

MD, thanks for replaying to my post.

Yes, both workbooks will be open at all the time and the ranges should be the same size. Why are you asking if they will be the same size?

What I meant was to link both workbooks with in the ranges (A:C1000) and (F:X1000) in both workbooks; Workbook1 (A:C1000) and (F:X1000) linked to workbook2 range (A:C1000) and (F:X1000) . I hope this is more understandable.

[mdmackillop]

Put into the sheet module of each book, changing names/sheet as required
[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("A1:C1000")) Is Nothing Or _
Not Intersect(Target, Range("F1:X1000")) Is Nothing Then
Workbooks("Book1.xls").Sheets(1).Range(Target.Address).Formula = Target.Formula
End If
Application.EnableEvents = True
End Sub

[/VBA]

[IgnBan]

MD, your code is awesome! You probably don’t realize how useful it is to me. It’s going to take me couple of day to find all the capabilities, It provides a solution for intended of the question , but this is opening so many possibilities to my projects. (infrastructure already set up in Excel needing Access capabilities, simple change ranges insert columns and use Vlookups, Indirect, MS Queries, etc, ) It may have it’s limitations but I think it has more advantages .

Thanks for sharing the knowledge, and let me learn from you!