Multi-document link question

**musik7** · 10-05-2012, 04:08 PM

I am not sure if this is the correct category for this, but maybe someone can still help. I have a master document, and then a couple dozen sheets in other documents that follow the same formatting, etc. What I would like to do is have rows 2-4 from the master document be shown exactly as they are on the child documents, and for changes to be reflected in those rows of the child documents as if the change was made locally.

I know I can use a formula to link the data, but it's more like I have 3 rows of header information that I would like to be reflected on these child documents, but any text that spans multiple cells is then cut off at the end of it's main cell, and if I add a column in the master sheet, the data is reflected correctly, but the formatting is not. What I have tried so far is to copy these rows and paste a link, but that seems to past individual links in all of the cells, and what I am thinkig of is more like one link for the entire 3 rows. What might work best is if I am able to have those rows in the child documents display the actual rows in the master document, much like a header does, but I don't know if this is possible.

Thank you in advance for any advice you are able to give.

Andrew

**fullysic** · 10-05-2012, 05:22 PM

Hi Andrew,

Just wanting to clarify on your requirements - so you have multiple workbooks, one is the master. In this master you essentially have a 'header' that is to appear in all child workbooks, and any changes to this header (including formatting, sizing etc) will be reflected in all of the 'child' workbooks? You have tried using formulas pointing to the master workbook, but any changes to the formatting in the master workbook is not appearing in the cells with the formula (as the formula is merely linking to the contents of the cell).

If the above is correct then the only way I can think to do it would be:

Use VBA code in the master document to update all child documents with the header
Use VBA code in each child document to update the header from the master documents (my preference)

Here's some code that I put together that would do option 2 for you. You could even automate the macro to run each time the document is opened so that it updates the header automatically without the user even knowing. To test this code out create a file master.xlsx and in the first three rows add some formatting (change column widths, font size, colour etc) and save. Paste the below code into another macro enabled workbook and change the code to point to the correct place where your master.xlsx file is. Once this is done, running the macro should import your header into the first three rows of the child workbook.

Sub ImportHeader()
    Dim MainWorkbook As Workbook
    Dim OtherWorkbook As Workbook
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    
    'sets the child workbook as the main workbook
    Set MainWorkbook = ThisWorkbook
    
    'opens the master with the header
    Workbooks.Open Filename:="E:\Master.xlsx", ReadOnly:=True
    
    'sets the currently open file as the other workbook
    Set OtherWorkbook = ActiveWorkbook
    
    'activates the main sheet in newly opened workbook. replace with name of your sheet
    Sheets("Main").Activate
    
    'copies header range
    Range("A1:ZZ3").Copy
    
    'activates original workbook
    MainWorkbook.Activate
    
    'selects start of header range
    Range("A1").Select
    
    'pastes header from master workbook
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    'closes master workbook
    OtherWorkbook.Close
        
End Sub

**musik7** · 10-06-2012, 12:08 AM

That is pretty accurate to what I am trying to do. The other thing that I hadn't mentioned, is that there are multiple headers per sheet, and they need to remain positioned correctly relative to the data, regardless of inserting and deleting rows for the amount of data that will be entered under each category. The headers on each sheet will be identical, just likely positioned differently depending on the data in each sheet. As such, I am only planning on having a single sheet in the master file for the child files/sheets to point to

I created a couple workbooks to illustrate some of these points as well. The ares outlined in red are the areas that need to be linked to/from the master. I would lean towards either linking the entire rows, or if that is more difficult, just including a bunch of extra cells to the right. The spacing between the categories on the first child sheet are different from the master file, but I didn't take the time to change the other child sheets. In the final application of this, there will be about 7 child files, each containing several sheets and gaining one additional sheet every week.

I have done a fair amount with formulas, but I've never worked with macros, so assume that I am clueless in that category. lol

Thank you again for your help.

Andrew

Master.xlsx
Child.xlsx

**fullysic** · 10-06-2012, 02:14 AM

Out of interest Andrew, what type of changes to the headers do you anticipate? Will these headers always remain the same size (3Row x 17Col)? Or will is be simple formatting changes such as colour, font, size etc?

**musik7** · 10-08-2012, 10:11 AM

I expect to insert columns, delete columns make text/fill color changes, change the cell contents, and possibly other types of changes that I don't anticipate at this time. I would default to linking the entire 3 rows of the headers, but if it's a lot easier to specify a smaller range, I would likely just specify 3 rows x 30 or 40 columns to make sure I won't run out of space. Each child sheet will also have different numbers of rows added and/or deleted between the category headers, so the headers need to update correctly regardless of where they are in that particular child sheet.

Thanks