vba 自动更新工作簿其他 Excel 表格中的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10724420/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Automatically Update Data in Other Excel Sheets of a Workbook
提问by user1413170
I have VBA code that takes my data on the "master" worksheet and puts it in the other sheets in a workbook. The problem I am having is that the new data doesn't update automatically. I would like to develop code that will automatically update my worksheets. This is the code that I have now.
我有 VBA 代码，它将我的数据放在“主”工作表上，并将其放在工作簿的其他工作表中。我遇到的问题是新数据不会自动更新。我想开发能够自动更新我的工作表的代码。这是我现在拥有的代码。
Sub test()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
If Range("B" & i).Value = "AP" Then Rows(i).Copy Destination:=Sheets("AP").Range("A" & Rows.Count).End(xlUp).Offset(1)
If Range("B" & i).Value = "*AP" Then Rows(i).Copy Destination:=Sheets(" If Range("B" & i).Value = "CSW" Then Rows(i).Copy Destination:=Sheets("CSW").Range("A" & Rows.Count).End(xlUp).Offset(1)
If Range("B" & i).Value = "CO" Then Rows(i).Copy Destination:=Sheets("CO").Range("A" & Rows.Count).End(xlUp).Offset(1)
If Range("B" & i).Value = "PSR" Then Rows(i).Copy Destination:=Sheets("PSR").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next i
End Sub
This puts the data in the other sheets, but when I input new data into the "master" worksheet, the data does not update in the other sheets. I've tried other methods to include auto filter, but they haven't worked.
这会将数据放在其他工作表中，但是当我将新数据输入“主”工作表时，其他工作表中的数据不会更新。我尝试了其他方法来包含自动过滤器，但它们没有奏效。
回答by Jon Crowell
Use the worksheet_changeevent in your "master" spreadsheet. When data is updated in the "master" sheet, it will raise the worksheet_changeevent and you can call your code to update the other sheets.
worksheet_change在“主”电子表格中使用该事件。当“主”表中的数据更新时，它会引发worksheet_change事件，您可以调用代码来更新其他表。
You can find detailed instructions on how to use it here: http://www.ozgrid.com/VBA/run-macros-change.htm
您可以在此处找到有关如何使用它的详细说明：http: //www.ozgrid.com/VBA/run-macros-change.htm
I set up a working example with your code. The workbook has 6 sheets ("master", "AP", "All AP", "CSW", "CO", and "PSR"). Row 1 in each sheet is assumed to be a header row. Once you have your workbook set up with the code below, any changes you make on the "master" sheet will raise the worksheet_change event, causing all of the destination sheets in the workbook to get updated with the most current data.
我用你的代码设置了一个工作示例。工作簿有 6 个工作表（“master”、“AP”、“All AP”、“CSW”、“CO”和“PSR”）。每个工作表中的第 1 行被假定为标题行。使用以下代码设置工作簿后，您在“主”工作表上所做的任何更改都会引发 worksheet_change 事件，从而导致工作簿中的所有目标工作表都使用最新数据进行更新。
Follow these steps to get it to work:
请按照以下步骤使其工作：
Add the following in the code module of the master sheet:
在主表的代码模块中添加以下内容：
_
_
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Call UpdateFromMaster
End Sub
Add these subs into a standard module:
将这些 sub 添加到标准模块中：
_
_
Sub UpdateFromMaster()
' clear whatever you had previously written to the destination sheets
Call ResetDestinationSheets
' the code you already had
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
If Range("B" & i).Value = "AP" Then Rows(i).Copy Destination:=Sheets("AP").Range("A" & Rows.Count).End(xlUp).Offset(1)
If Range("B" & i).Value = "*AP" Then Rows(i).Copy Destination:=Sheets("All AP").Range("A" & Rows.Count).End(xlUp).Offset(1)
If Range("B" & i).Value = "CSW" Then Rows(i).Copy Destination:=Sheets("CSW").Range("A" & Rows.Count).End(xlUp).Offset(1)
If Range("B" & i).Value = "CO" Then Rows(i).Copy Destination:=Sheets("CO").Range("A" & Rows.Count).End(xlUp).Offset(1)
If Range("B" & i).Value = "PSR" Then Rows(i).Copy Destination:=Sheets("PSR").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next i
End Sub
_
_
Sub ResetDestinationSheets()
'== not elegant, but will work in your example
Call ResetThisSheet("AP")
Call ResetThisSheet("ALL AP") ' I didn't know what you called this sheet
Call ResetThisSheet("CSW")
Call ResetThisSheet("CO")
Call ResetThisSheet("PSR")
End Sub
_
_
Sub ResetThisSheet(ByRef SheetToClear As String)
Sheets(SheetToClear).Range("A2:B" & Rows.Count).Clear
End Sub
回答by Scott Holtzman
To provide an alternative to the worksheet_change() event, which will trigger the code every time a change is made to the worksheet, which you may or may not want. You can also create a shape (or button) and assign your code to that button, so it only runs when you (or the user) tells it to. The advantage of this over the worksheet change() event is that the code won't fire with every little change to the worksheet, which as I stated above, may or may not be desirable.
提供 worksheet_change() 事件的替代方法，该事件将在每次对工作表进行更改时触发代码，您可能想要也可能不想要。您还可以创建一个形状（或按钮）并将您的代码分配给该按钮，这样它只会在您（或用户）告诉它时运行。与工作表 change() 事件相比，这样做的优点是代码不会随着工作表的每一个微小变化而触发，正如我上面所说的，这可能是也可能不是可取的。
To assign a macro to a button or shape, add the shape to your sheet, and then right-click and choose Assign Macro. Also see here... How Can I Tie a VBA Macro To A Button In Excel
要将宏分配给按钮或形状，请将该形状添加到您的工作表，然后右键单击并选择“分配宏”。另请参阅此处... 如何将 VBA 宏绑定到 Excel 中的按钮
