MACRO TO LINK DATA !

[jay dean]

In my Excel Workbook 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 on the same row in column B 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 on the same row in column D of sheet X
and paste it as a Link into the range B20:B20 of the sheet whose name we
matched in column A.

Any assistance would be very appreciated. Thanks.

Jay Dean


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

 

[Bernie Deitrick]

Jay,

Instead of a macro, how about formulas?

In cell B18 of all the sheets except "Sheet X"
=SheetName()
In B19 of all sheets:
=VLOOKUP(B18,'Sheet X'!A:B,2,FALSE)
In B20 of all sheets:
=VLOOKUP(B18,'Sheet X'!A,4,FALSE)

You'll need to put this code into a regular codemodule of your
worksheet:

Function SheetName() As String
SheetName = Application.Caller.Parent.Name
End Function

HTH,
Bernie
MS Excel MVP

 

[jay dean]

I would need a macro. Any help would be apprecaited. Thanks.

Jay Dean

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

 

[Bernie Deitrick]

Jay,

If Sheet X column A has the worksheet names:

Sub MakeJaysLinks()
Dim myCell As Range
On Error Resume Next
For Each myCell In Worksheets("Sheet X"). _
Range("A:A").SpecialCells(xlCellTypeConstants, 2)
Worksheets(myCell.Value).Range("B19").Formula = _
"='Sheet X'!" & Cells(myCell.Row, 2).Address(False, False)
Worksheets(myCell.Value).Range("B20").Formula = _
"='Sheet X'!" & Cells(myCell.Row, 4).Address(False, False)
Next myCell
End Sub

HTH,
Bernie
MS Excel MVP

 

[jay dean]

Bernie -
It works perfectly. Thanks a lot !

Jay Dean !


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