Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO TO LINK DATA !
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO TO LINK DATA !
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:D,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" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO TO LINK DATA !
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO TO LINK DATA !
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" wrote in message ... 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO TO LINK DATA !
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro Excel problem link cells with Data-Validation option | Excel Discussion (Misc queries) | |||
macro to link worksheets | Excel Discussion (Misc queries) | |||
using macro that will link into web | Excel Discussion (Misc queries) | |||
Link a macro to a cell value | Excel Programming | |||
Macro To Update Data Table On DDE Link Change | Excel Programming |