如何使用 Excel 中的 VBA 将列从一张工作表复制到另一张工作表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1990620/
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
How can I copy columns from one sheet to another with VBA in Excel?
提问by excel34
I'm trying to write a macro that copies the content of column 1 from sheet 1 to column 2 on sheet 2. This is how the module looks like but, when I run it, I get
我正在尝试编写一个宏，将第 1 列的内容从工作表 1 复制到工作表 2 的第 2 列。这就是模块的样子，但是，当我运行它时，我得到
Run time error 9, Subscript out of range.
运行时错误 9，下标超出范围。
Sub OneCell()
Sheets("Sheet1").Select
'select column 1 A1'
Range("A1:A3").Select
Selection.Copy
Range("B1:B3").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
Application.CutCopyMode = False
End Sub
回答by David
The following works fine for me in Excel 2007. It is simple, and performs a full copy (retains all formatting, etc.):
以下在 Excel 2007 中对我来说很好用。它很简单，并执行完整副本（保留所有格式等）：
Sheets("Sheet1").Columns(1).Copy Destination:=Sheets("Sheet2").Columns(2)
"Columns" returns a Range object, and so this is utilizing the "Range.Copy" method. "Destination" is an option to this method - if not provided the default is to copy to the paste buffer. But when provided, it is an easy way to copy.
“Columns”返回一个 Range 对象，因此这是利用“Range.Copy”方法。“目标”是此方法的一个选项 - 如果未提供，则默认为复制到粘贴缓冲区。但是当提供时，它是一种简单的复制方法。
As when manually copying items in Excel, the size and geometry of the destination must support the range being copied.
与在 Excel 中手动复制项目时一样，目标的大小和几何形状必须支持被复制的范围。
回答by guitarthrower
Selecting is often unnecessary. Try this
选择通常是不必要的。尝试这个
Sub OneCell()
Sheets("Sheet2").range("B1:B3").value = Sheets("Sheet1").range("A1:A3").value
End Sub
回答by Lucy
If you have merged cells,
如果您合并了单元格，
Sub OneCell()
Sheets("Sheet2").range("B1:B3").value = Sheets("Sheet1").range("A1:A3").value
End Sub
that doesn't copy cells as they are, where previous code does copy exactly as they look like (merged).
这不会按原样复制单元格，而先前的代码确实按照它们的外观（合并）进行复制。
回答by paxdiablo
I'm not sure why you'd be getting subscript out of range unless your sheets weren't actually called Sheet1or Sheet2. When I rename my Sheet2to Sheet_2, I get that same problem.
我不确定为什么你会得到超出范围的下标，除非你的工作表实际上没有被调用Sheet1或Sheet2. 当我将 my 重命名Sheet2为 时Sheet_2，我遇到了同样的问题。
In addition, some of your code seems the wrong way about (you paste before selecting the second sheet). This code works fine for me.
此外，您的某些代码似乎是错误的方式（您在选择第二张纸之前粘贴）。这段代码对我来说很好用。
Sub OneCell()
Sheets("Sheet1").Select
Range("A1:A3").Copy
Sheets("Sheet2").Select
Range("b1:b3").Select
ActiveSheet.Paste
End Sub
If you don't want to know about what the sheets are called, you can use integer indexes as follows:
如果您不想知道工作表的名称，您可以使用整数索引，如下所示：
Sub OneCell()
Sheets(1).Select
Range("A1:A3").Copy
Sheets(2).Select
Range("b1:b3").Select
ActiveSheet.Paste
End Sub
回答by Ravi
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, r As Range
Set rng = Intersect(Target, Range("a2:a" & Rows.Count))
If rng Is Nothing Then Exit Sub
For Each r In rng
If Not IsEmpty(r.Value) Then
r.Copy Destination:=Sheets("sheet2").Range("a2")
End If
Next
Set rng = Nothing
End Sub
