DATA LINKING MACRO !

[jay dean]

I have been able to come up with some code for the following senario,
but it needs a fix. Would someone please take a look at my code posted
below the senario help me with it. I am not good at programming, but I
know this is possible.

This is the Senario:
In my Excel Workbook called "Pool.xls" there are many sheets among
which is sheet X . In
sheet X, column A contains the names of all the other sheets in the
workbook, and columns B and D contain data.
I need a macro that will loop through all the other sheets in the
workbook doing the following :

-If the name of any sheet is equal to the name in Column A of sheet X,
then
(1) copy the corresponding data in column B on the same row of sheet X
and paste it as a Link into the range B19:B19 of the sheet whose name we
matched in column A.
and
(2) copy the corresponding data in column D on the same row of sheet X
and paste it as a Link into the range B20:B20 of the sheet whose name we
matched in column A.

(3) If the ranges to be copied do not contain data, then
do nothing

This is the code I have come up with and need help with --

Dim ws As Worksheet
Dim copy1 As Range
Dim copy2 As Range
Dim copy3 As Range

Dim rng1 As Range
Dim rng2 As Range

Set copy1 = Workbooks("Pool.xls").Worksheets("x"). _
Range("A5:A5")

Set copy2 = Workbooks("Pool.xls").Worksheets("x"). _
Range("B5:B5")

Set copy3 = Workbooks("Pool.xls").Worksheets("x"). _
Range("D55")


Set ws = Workbooks("Pool.xls").ActiveSheet

Set rng1 = ws.Range("B19:B19")

Set rng2 = ws.Range("B20:B20")


For Each ws In ActiveWorkbook.Worksheets

If (copy1.Value = ws.Name) Then

Do Until Intersect(copy1, copy1.Parent.UsedRange) Is Nothing

copy2.Copy
ws.Range(rng1.Address).Paste Link:=True

copy3.Copy
ws.Range(rng2.Address).Paste Link:=True

Set copy1 = copy1.Offset(1)
Set copy2 = copy2.Offset(1)
Set copy3 = copy3.Offset(1)
Set rng1 = rng1.Offset(0)
Set rng2 = rng2.Offset(0)

Application.CutCopyMode = False

Loop

End If

Next ws

-Any assistance would be appreciated. I would really need the macro, so
please help!

Thanks.
Jay Dean





*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!