vba 将范围从一个工作表复制/粘贴到另一工作表中的下一个可用列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/48192496/
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
Copy/paste range from one worksheet to next available column in another worksheet
提问by P. Saha
I am trying to copy a range of data from one worksheet into another worksheet in the same workbook.
我正在尝试将一系列数据从一个工作表复制到同一工作簿中的另一个工作表中。
So copy range("A2:B10") in Sheet1 then paste in ("A2") in Sheet2.
因此，在 Sheet1 中复制 range("A2:B10") 然后在 Sheet2 中粘贴 ("A2")。
Each time the macro is run, I would like the values in ("Sheet 1").Range("A2:B10") to be pasted in consecutive columns, so in the second run, in "B2""C2" in Sheet 2.
每次运行宏时，我都希望将 ("Sheet 1").Range("A2:B10") 中的值粘贴到连续列中，因此在第二次运行中，在 Sheet中的"B2""C2" 2.
I have the first part, but struggling with the 2nd part:
我有第一部分，但在第二部分挣扎：
Sub sbCopyRangeToAnotherSheet()
'Set range
Sheets("Sheet1").Range("A2:B10").Copy Destination:=Sheets("Sheet2").Range("A2")
'Copy the data
Sheets("Sheet1").Range("A2:B10").Copy
'Activate the destination worksheet
Sheets("Sheet2").Activate
'Select the target range
Range("A2").Select
'Paste in the target destination
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
回答by JNevill
Your code is odd
你的代码很奇怪
This line Sheets("Sheet1").Range("A2:B10").Copy Destination:=Sheets("Sheet2").Range("A2")is copying and pasting all in one line. So your subsequent lines where you .copy.activate.selectand .pasteare redundant. Your first line already did all of this.
这一行Sheets("Sheet1").Range("A2:B10").Copy Destination:=Sheets("Sheet2").Range("A2")是在一行中复制和粘贴。所以你随后的行你.copy.activate.select和.paste是多余的。您的第一行已经完成了所有这些。
You need to identify the last used column in Row "A" of Sheet2. To do this you can use the .End()method of a range to find the first occupied cell in the row. Then use that cell as your copy Destination:
您需要确定 Sheet2 的“A”行中最后使用的列。为此，您可以使用.End()范围的方法来查找行中第一个被占用的单元格。然后使用该单元格作为您的副本Destination：
Sub sbCopyRangeToAnotherSheet()
'Declare a variable to hold the cell to which we will paste the data
Dim pasteCell as Range
'Set the variable to the first empty cell in Row 2
Set pasteCell = Sheets("Sheet2").Range("IV2").end(xlToLeft).Offset(,1)
'Copy and paste
Sheets("Sheet1").Range("A2:B10").Copy Destination:=pasteCell
End Sub
回答by Vityata
This is something that will do the job without using Activeand Select:
这是不使用Activeand就可以完成工作的东西Select：
Option Explicit
Sub TestMe()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim source As Range
Dim target As Range
Dim lastColumn As Long
Set ws1 = Worksheets(1)
Set ws2 = Worksheets(2)
With ws2
lastColumn = .Cells(2, .Columns.Count).End(xlToLeft).Column
If WorksheetFunction.CountA(.Columns(1)) > 0 Then
lastColumn = lastColumn + 1
End If
End With
Set source = ws1.Range("A2:A10")
Set target = ws2.Cells(2, lastColumn)
source.Copy destination:=target
Application.CutCopyMode = False
End Sub
The trickiest part in the code is to start writing on the correct column on the target worksheet. If the worksheet is empty, then the correct column is the first one. In every other case, the correct column is the last column + 1.
代码中最棘手的部分是在目标工作表的正确列上开始书写。如果工作表为空，则正确的列是第一列。在所有其他情况下，正确的列是最后一列 + 1。
The standard way of finding last used column - Range.End(xlToLeft).Columnwould always return the first column as a last used column, w/o paying attention whether it is used or not. Thus you need some kind of a check, to know whether it is empty or not: WorksheetFunction.CountA(.Columns(1)) > 0
查找上次使用的列的标准方法 -Range.End(xlToLeft).Column总是将第一列作为上次使用的列返回，而无需注意它是否被使用。因此，您需要进行某种检查，以了解它是否为空：WorksheetFunction.CountA(.Columns(1)) > 0
