formula needed

[ericlch16]

I have a workbook1!sheet1

it has dynamic columns, rows

i have another workbook2!sheet1 which is linked to workbook1!sheet1.basically A1 of workbook1 is linked to a1 to workbook2. so whatever is inserted to cells(1,1) is then replicated in workbook.

workbook1 can have various rows depending on the date. it can have 200 rows while on another date can have 60000 rows. columns is fixed

to link workbook2 to workbook1, i have created a formula for each cell of workbook2 so that it is =workbook1!sheets!A1 in ALL the workbook i.e 1million rows [the max rows in excel 2007]. as you can imagine this is a lot of linking and hence performance of the excel becomes slow.

how to create a dynamic link in workbook2 i.e when workbook1 has 20 rows, only the 20 rows should be linked in workbook2. i dont need formulas to be in all the rows. i dont want to look at workbook1 to see how many rows there are and then copy the link up to rows 20 in workbook2. it is tedious when you have to do this in a daily basis. how to create a dynamic list of linking in workbook2?

any help?

 

[Peter_SSs]

This is a macro approach that doesn't link the two sheets by formulas, but instead each time something is entered in or deleted from a cell (or multiple cells) of the sheet in workbook1 the whole worksheet is copied into workbook2 and if there is an existing sheet in workbook2 with that name it is deleted. Not sure if that will suit your purpose or not.

It assumes that you will have workbook2 open when you are working on workbook1. If it is not a message will let yopu know and advise that an update has not been done to workbook2. In that case you could open workbook2 then in the sheet in workbook1 just select, say, an empty cell and press the Delete key. That should force the update to workbook2.

Before testing, make sure you have backup copies of both workbooks.

To implement ..

1. Right click the relevant sheet name tab in workbook1 and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window (after editing the code for your workbook names).

4. Try making some changes in the relevant sheet in workbook1

Macros will need to be enabled.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> wb1 <SPAN style="color:#00007F">As</SPAN> Workbook, wb2 <SPAN style="color:#00007F">As</SPAN> Workbook, wb <SPAN style="color:#00007F">As</SPAN> Workbook<br>    <SPAN style="color:#00007F">Dim</SPAN> wsinwb2 <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> shName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> bExists <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> wb1Name <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "Workbook1.xlsm" <SPAN style="color:#007F00">'<- Change to suit</SPAN><br>    <SPAN style="color:#00007F">Const</SPAN> wb2Name <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "Workbook2.xlsm" <SPAN style="color:#007F00">'<- Change to suit</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> ThisWorkbook.Name <> wb1Name <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> wb <SPAN style="color:#00007F">In</SPAN> Workbooks<br>        <SPAN style="color:#00007F">If</SPAN> wb.Name = wb2Name <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> wb2 = wb<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> wb<br>    <SPAN style="color:#00007F">If</SPAN> wb2 <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        MsgBox wb2Name & " is not open, unable to update"<br>        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#00007F">Else</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> ActiveSheet<br>            shName = .Name<br>            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> wsinwb2 = wb2.Sheets(shName)<br>            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>            <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> wsinwb2 <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> bExists = <SPAN style="color:#00007F">True</SPAN><br>            .Copy Before:=wb2.Sheets(1)<br>            .Activate<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> bExists <SPAN style="color:#00007F">Then</SPAN><br>            Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN><br>            wb2.Sheets(shName).Delete<br>            Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN><br>            wb2.Sheets(1).Name = shName<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br></FONT>