Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
DATA LINKING MACRO !
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("D5:D5") 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro linking to wrong workbook | Excel Discussion (Misc queries) | |||
Linking Macro to Button? | Excel Discussion (Misc queries) | |||
Inserting a button into a cell and linking it to a macro | Excel Discussion (Misc queries) | |||
Linking a Macro to a Button | Excel Discussion (Misc queries) | |||
Macro linking | Excel Discussion (Misc queries) |