java 多表 修改数据_从多个Excel工作表更新数据

我有关于如何将多个Excel工作表中的数据更新到主工作表中的一列的问题 . 每当我们输入一个新行时，我希望它在主表中自动更新 . 该值为id，因此它在多个工作表中是唯一的 . 例如：

Excel表1：

ID

---

1

2

3

4

5

Excel表2：

ID

---

12

23

34

41

53

Excel表3：

ID

---

123

215

324

445

562

掌握Excel表格：

ID

---

1

2

3

4

5

12

23

34

41

53

123

215

324

445

562

每当我输入新值6(对于工作表1)时，它将更新为主表 .

我能这样做吗？我们需要使用宏吗？谢谢 .

使用SheetChange()更新代码

With DataEntrySheet

'** Set variables for using the Find method in a loop

Set loopRng = DataEntrySheet.Columns(1)

Set lookRng = MasterSheet.Columns(2)

'** Set the range which will be used to write data if found

Set OldLastCell = DoNotEditSheet.Range( _

"C65536").End(xlUp).Offset(1)

'** Start the Find loop

For Each iCel In loopRng

'** Using the Find method to find the cell value.

Set foundRng = lookRng.Find(iCel.Value, lookRng.Cells(1), _

xlValues, xlWhole, MatchCase:=True)

'** Test if the variable 'foundRng' is Nothing. If the value

' was not found, the variable will be Nothing, or else it

' will be the Range Object of that (first) found value.

If foundRng Is Nothing Then

'** Use the two ranges to transfer data (faster than Copy/Paste).

OldLastCell.Value = iCel.Value

OldLastCell.Offset(, -1).Value = iCel.Offset(, -1).Value

OldLastCell.Offset(, 2).Value = iCel.Offset(, 1).Value

OldLastCell.Offset(, 5).Value = iCel.Offset(, 2).Value

OldLastCell.Offset(, 10).Value = iCel.Offset(, 3).Value

'** Reset the variable to be one row below where we wrote the

' data to. This will keep the data organized by rows.

Set OldLastCell = OldLastCell.Offset(1)

End If

'** This MUST be set to Nothing before the next iteration. If not,

' and a match is not found (following a good find) then you may

' have mismatched iterations with false results.

Set foundRng = Nothing

Next iCel

End With

结束子