vba 将一张 Excel 表格中的列镜像到其他多个 Excel 表格并自动更新

Mirroring column in one excel sheet to other multiple excel sheets with automatic updating

提问by user1628421

I have an excel workbook that contains multiple sheets within it. For the sake of this question, the sheets are named Sheet1, Sheet2, Sheet3, and so on. I would like to have Column A from sheet1 be replicated throughout the rest of the sheets and as new cells are added to column A in sheet1, they would automatically be entered into the other sheets within the workbook. I would prefer not to have a set "ending range; ie: A100000" for this. For example, if I enter First in cell A1 of Sheet1, the word "First" should now also appear in cell A1 of Sheet2. I have used the following code, and it does not seem to work. Any help would be greatly appreciated.

我有一个 Excel 工作簿，其中包含多个工作表。对于这个问题，工作表被命名为 Sheet1、Sheet2、Sheet3，依此类推。我希望将 sheet1 中的 A 列复制到整个工作表的其余部分，并且随着新单元格添加到 sheet1 中的 A 列，它们将自动输入到工作簿中的其他工作表中。我不想为此设置一个“结束范围；即：A100000”。例如，如果我在 Sheet1 的单元格 A1 中输入 First，那么“First”这个词现在也应该出现在 Sheet2 的单元格 A1 中。我使用了以下代码，它似乎不起作用。任何帮助将不胜感激。

Private Sub Worksheet_Change(ByVal Target As Range)
        Call UpdateFromSheet1
End Sub

Sub UpdateFromSheet1(ByVal Sh As Object, ByVal Target As Range)
    If Sh.CodeName = "Sheet1" Then
        If Not Intersect(Target(1, 1), Range("A1:A1000")) Is Nothing Then
            Sh.Range("A1:A1000").Copy Sheet2.Range("A1")
        End If
    End If
End Sub

回答by Scott Holtzman

UPDATE

更新

For a clean looking Non-VBAsolution, you can use the formula references that others have mentioned, but enter it like this.

对于干净的非 VBA解决方案，您可以使用其他人提到的公式引用，但像这样输入。

In Sheet2 cell A1 = If(Sheet1!A1="","",Sheet1!A1)That way you can fill down on the whole of column A and not have "0" pop-up if Sheet1 has a rows without data.

在 Sheet2 单元格 A1 =If(Sheet1!A1="","",Sheet1!A1)这样你可以填充整个 A 列，如果 Sheet1 有没有数据的行，则不会弹出“0”。

I think you have the general idea, but I suspect you may not have your code in the right place.

我认为您有大致的想法，但我怀疑您的代码可能没有放在正确的位置。

For the VBAsolution:

对于VBA解决方案：

First, you don't need to call the sub from Worksheet_Changeevent (unless of course you want to use this sub for other reasons and pass variables to it. Second, if you place this code in the worksheet object in the VBE of the "Sheet1" it will do as you wish:

首先，您不需要从Worksheet_Change事件中调用子程序（当然，除非您出于其他原因想要使用此子程序并将变量传递给它。第二，如果您将此代码放置在“Sheet1”的 VBE 中的工作表对象中"它会如你所愿：

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Columns("A")) Is Nothing Then

    Dim wks As Worksheet
    For Each wks In Sheets(Array("Sheet2", "Sheet3"))

        Target.EntireColumn.Copy wks.Columns(1)

    Next

End If


End Sub

回答by Stepan1010

This is a very basic use for excel. You can set cells equal to each other. You in no way would need a VBA macro for this.

这是excel的一个非常基本的用法。您可以将单元格设置为彼此相等。为此，您绝不会需要 VBA 宏。

If you put this in cell "A1" on Sheet2 and Sheet3:

如果你把它放在 Sheet2 和 Sheet3 上的单元格“A1”中：

=Sheet1!A1

Then when you type something into A1, it will be "mirrored" on sheets 2 and 3. You can autofill this down to all the cells in column A on sheets 2 and 3.

然后，当您在 A1 中键​​入内容时，它会在第 2 和第 3 页上“镜像”。您可以将其自动填充到第 2 和第 3 页上 A 列中的所有单元格。

Are you familiar with the term autofill?

您熟悉术语自动填充吗？

If you don't understand anything I just said and you want to just run a macro then run this and start typing away:

如果您不明白我刚才说的任何内容，并且只想运行一个宏，然后运行它并开始输入：

Sub MacroBasicQuestion()

Dim wbk As Workbook
Set wbk = ThisWorkbook

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Set ws1 = wbk.Sheets(1)
Set ws2 = wbk.Sheets(2)
Set ws3 = wbk.Sheets(3)

Dim cell As Range

Set cell = ws2.Range("A1:A1")
ws2.Select
cell.FormulaR1C1 = "=Sheet1!RC"
cell.Select
Selection.AutoFill Destination:=Range("A:A"), Type:=xlFillDefault

Set cell = ws3.Range("A1:A1")
ws3.Select
cell.FormulaR1C1 = "=Sheet1!RC"
cell.Select
Selection.AutoFill Destination:=Range("A:A"), Type:=xlFillDefault

End Sub

Good Luck.

祝你好运。