vba 在excel vba的第一个空列中复制粘贴范围

copy paste range in first empty column in excel vba

提问by user2172916

I want to copy the range of cells (C1:Z1000) of worksheet3 and paste them to the first empty column of worksheet1 (in row 1). The code below blocks at the last line: source.Range("C1:Z1000").Copy destination.Cells(1, emptyColumn)

我想复制 worksheet3 的单元格范围 (C1:Z1000) 并将它们粘贴到 worksheet1 的第一个空列（第 1 行）。下面的代码块在最后一行： source.Range("C1:Z1000").Copy destination.Cells(1, emptyColumn)

Sub CopyRange()

Dim source As Worksheet
Dim destination As Worksheet
Dim emptyColumn As Long

Set source = Sheets("Sheet3")
Set destination = Sheets("Sheet1")

'find empty Column (actually cell in Row 1)'
emptyColumn = destination.Cells(1, destination.Columns.Count).End(xlUp).Column
If emptyColumn > 1 Then
emptyColumn = emptyColumn + 1
End If

source.Range("C1:Z1000").Copy destination.Cells(1, emptyColumn)

End Sub

回答by StoriKnow

I think your issue was the way you were obtaining the emptyColumnvalue, as others suggested. This works for me:

我认为你的问题是你获得emptyColumn价值的方式，正如其他人所建议的那样。这对我有用：

Sub CopyRange()

Dim source As Worksheet
Dim destination As Worksheet
Dim emptyColumn As Long

Set source = Sheets("Sheet3")
Set destination = Sheets("Sheet1")

'find empty Column (actually cell in Row 1)'
emptyColumn = destination.Cells(1, destination.Columns.Count).End(xlToLeft).Column
If emptyColumn > 1 Then
emptyColumn = emptyColumn + 1
End If

source.Range("C1:Z1000").Copy destination.Cells(1, emptyColumn)

End Sub

The way you currently have it will pull the very last column in the worksheet, which seems to thrown an error when pasting to it. The above approach will pull the very first empty column. That is, if column C is empty, the value of emptyColumnwill be 3

您目前使用它的方式将拉出工作表中的最后一列，粘贴时似乎会引发错误。上述方法将拉出第一个空列。也就是说，如果 C 列为空，则值为emptyColumn3

回答by Olle Sj?gren

Have you tried stepping through your code?

你试过单步执行你的代码吗？

If you do, you'll notice that the following line will always set the emptyColumnsvariable to the far right column, regardless of which columns are used:

如果这样做，您会注意到以下行将始终将emptyColumns变量设置为最右侧的列，而不管使用哪些列：

emptyColumn = destination.Cells(1, destination.Columns.Count).End(xlUp).Column

By adding 1 to it and pasting you try to paste to a column that does not exist. That will give you an error every time.

通过向其中添加 1 并粘贴，您可以尝试粘贴到不存在的列。那每次都会给你一个错误。

Instead, try the following to find the last used column. It searches from the far right column in the first row and goes left (like typing CTRL+LEFT), in order to find the last used column:

相反，请尝试以下操作以查找最后使用的列。它从第一行最右侧的列开始搜索并向左移动（如输入CTRL+ LEFT），以找到最后使用的列：

emptyColumn = destination.Cells(1, destination.Columns.Count).End(xlToLeft).Column

Then you can add 1 to it and paste.

然后你可以添加 1 并粘贴。

回答by Our Man in Bananas

try this:

尝试这个：

emptyColumn = destination.Cells(1, destination.Columns.Count).End(xltoright).Column

source.Range("C1:Z1000").Copy Destination:= Cells(1, emptyColumn)

named argumentsare followed by a colon equals :=

命名参数后跟一个冒号等于:=

your code for End should be:End(xlToRight)

你的 End 代码应该是：End(xlToRight)

another alternative would be:

另一种选择是：

source.Range("C1:Z1000").Copy 
with destination
    .cells(1,emptycolumn).select
    .paste
end with

I hope that helps

我希望有帮助

Philip

菲利普

回答by user3777983

I found this post, modified it to fit my needs. Will paste transpose

我找到了这篇文章，对其进行了修改以满足我的需要。将粘贴转置

Sub Transpose_PasteModified()

Dim source As Worksheet
Dim destination As Worksheet
Dim emptyColumn As Long

Set source = Sheets("Sheet1")
Set destination = Sheets("Sheet2")
'Data Source
source.Range("A3:C3").Copy
'Gets Empty Column
emptyColumn = destination.Cells(3, destination.Columns.Count).End(xlToLeft).Column

'In order to avoid issues of first row returning 1
'in this case #3 is the row so we're checking A3
'If row changes then change A3 to correct row

If IsEmpty(destination.Range("A3")) Then
destination.Cells(3, 1).PasteSpecial Transpose:=True
Else
emptyColumn = emptyColumn + 1
destination.Cells(3, emptyColumn).PasteSpecial Transpose:=True
End If

End Sub