将工作表链接到主工作表并在不存在主工作表的情况下保留值? Excel 2010 [英] Linking worksheets to a master worksheet and keeping values if the master worksheet is not present? Excel 2010

问题描述

我有很多工作表，目前，每3个月我必须逐一检查这些工作表，以更新几个单元格中的信息.

I have many worksheets and at present, every 3 months I have to go through these one by one to update information in just a few cells.

如果我可以创建一个主源文件，其中包含要更新的这几个单元格，所有工作表都链接到该源文件，这样我就可以更新此文件，然后所有文件都将更新，那就太好了.

It would be great if I could create a master source file containing these few cells that get updated, that all my worksheets linked to, so that I could just update this file and all my files would then update.

我唯一的麻烦是，然后我通过电子邮件将这些工作表发送给客户，每个客户都有一个特定的工作表.

The only trouble I have is that I then send these worksheets out to clients by email, each client gets a specific worksheet.

这意味着到主源文件的本地链接将不再存在，并且我假设会出现错误.有什么方法可以将所需的文件链接到主文件，能够更新主文件并相应地更新所有其他文件，但是仅将单个文件发送给客户端，并保留来自主文件.

This would mean that the local links to the master source file would no longer be there and I'm assuming there would be errors. Is there some way that I could link my files, as desired, to a master file, be able to update the master file and have all the other files update accordingly, but then send only a single file to a client and keep the values from the master file.

我希望这是有道理的！我想做的事情很简单，把它写成文字有点棘手.

I hope this is making sense! It's quite simple what I want to do, it's just a bit tricky putting it into words.

任何帮助或建议都很好！

Any help or advice would be great!

推荐答案

您会认为有一种简单的方法可以立即执行此操作，但是确实存在问题.这个不太好用的解决方案显示了如何在更新客户工作表时从必须打开的主工作簿中复制单元格或范围.显然，客户端用户不会拥有主工作簿，因此在这种情况下宏会静默失败.将此代码放在每个客户端工作簿的ThisWorkbook模块中.

You would think there would be a simple way to do this out of the box, but it does present a problem. This not very elegant solution shows how to copy cells or ranges from a master workbook that you must have open when you are updating your client sheets. Obviously the client user won't have the master workbook and so the macro fails silently in that case. Put this code in your ThisWorkbook module of each client workbook.

Private Sub Workbook_Open()
    On Error Resume Next
    Dim master As Workbook
    Set master = Workbooks("master.xlsm")

    If master Is Nothing Then
    'the client is probably opening the wbook. do nothing
    Else 'copy your stuff here
        With Workbooks("master.xlsm")
            .Worksheets("Sheet1").Range("A1:D4").Copy _
                Destination:=Worksheets("Sheet1").Range("A1:D4")
        End With
    End If

End Sub

这篇关于将工作表链接到主工作表并在不存在主工作表的情况下保留值? Excel 2010的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！