当另一张纸的另一个相应的单元格填充相同的值时，如何使excel自动填充单元格的单元格中的值？ [英] How to make excel automatically fill a value in a cell of a sheet when another corresponding cell of another sheet is filled the same value?

问题描述

例如，我在表格1中有单元格A1，其值为是。如果我填写sheet2 ='sheet1'！A1 中的单元格A1，那么我们知道发生了什么。然而，当我填写sheet1的单元格A2时，如何使sheet2中的单元格A2也具有是，而不填充sheet2的单元格A2 ='sheet1'！A2 ？

换句话说，我想使excel自动填充sheet2的单元格，每次当我填充sheet1的相应单元格中的值时。

$ b $

  Private Sub Worksheet_Change（ByVal Target As Range）
 Application.EnableEvents = False 
 Dim r As Range 
 For each r In Target 
 addy = r.Address 
表（Sheet2）。范围（addy）.Value = r.Value 
下一步r 
 Application.EnableEvents = True 
 End Sub 
  

因为它是工作表代码，它很容易安装和自动使用：

1. 右键单击Excel窗口底部附近的选项卡名称


2. 选择查看代码 - 这将打开一个VBE窗口


3. 将东西粘贴并关闭VBE窗口

如果您有任何疑问，请先尝试使用试用版。

如果保存工作簿，宏将被保存。
如果您在2003年之后使用Excel版本，则必须将
保存为.xlsm而不是.xlsx

要删除宏：

1. 打开上面的VBE窗口


2. 清除代码


3. 关闭VBE窗口

要了解有关宏的更多信息，请参阅：

http://www.mvps.org/dmcritchie/ excel / getstarted.htm

和

http://msdn.microsoft.com/en-us/library/ee814735（v = office .14）.aspx

要了解有关事件宏（工作表代码）的更多信息，请参阅：

http://www.mvps.org/dmcritchie/excel/event.htm

必须启用宏才能使其工作！

For example, I have cell A1 in sheet 1 and its value is "yes". If I fill in the cell A1 in sheet2 ='sheet1'!A1 then we know what is going on. However, how can I make the cell A2 in sheet2 also have "yes" when I fill in cell A2 of sheet1 without filling in cell A2 of sheet2 ='sheet1'!A2 ?

In other word, I want to make excel automatically fill a cell of sheet2 each time when I fill a value in corresponding cell of sheet1.

解决方案

Enter the following event macro in the Sheet1 code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
        Dim r As Range
        For Each r In Target
            addy = r.Address
            Sheets("Sheet2").Range(addy).Value = r.Value
        Next r
    Application.EnableEvents = True
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

这篇关于当另一张纸的另一个相应的单元格填充相同的值时，如何使excel自动填充单元格的单元格中的值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！