PDA

View Full Version : Solved: How to Link two sets of ranges



IgnBan
08-09-2008, 02:15 PM
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:thumb

IgnBan
08-09-2008, 03:43 PM
OK, lets make it shorter (A:C1000) and (F:X1000)

mdmackillop
08-09-2008, 03:59 PM
Will both books be open? Don't the ranges need to be the same size?

IgnBan
08-09-2008, 04:13 PM
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
08-09-2008, 05:30 PM
Put into the sheet module of each book, changing names/sheet as required
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

IgnBan
08-09-2008, 06:33 PM
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!