VBA - 复制区域和粘贴区域的大小不一样错误？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43950111/
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
VBA - copy area and paste area aren't the same size error?
提问by jhovyn
Good Day! I have these code below which it gives me an error of "We can't paste because copy area and paste area arent the same size".. Please help what is wrong with these code... :(
再会！我在下面有这些代码，它给了我一个错误“我们无法粘贴，因为复制区域和粘贴区域的大小不同”..请帮助这些代码有什么问题...... :(
Option Explicit
Sub CopyRangeFromMultiWorksheets()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim CopyRng As Range
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("RDBMergeSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "RDBMergeSheet"
For Each sh In ActiveWorkbook.Worksheets
If IsError(Application.Match(sh.Name, _
Array(DestSh.Name, "Information"), 0)) Then
Last = LastRow(DestSh)
If sh.Name = "Sheet1" Then
Set CopyRng = sh.Range("A:G")
End If
If sh.Name = "Sheet2" Then
Set CopyRng = sh.Range("B:G")
End If
If sh.Name = "Sheet3" Then
Set CopyRng = sh.Range("C:G")
End If
CopyRng.Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With
End If
Next
ExitTheSub:
Application.GoTo DestSh.Cells(1)
DestSh.Columns.AutoFit
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Below is my Function
下面是我的功能
Option Explicit
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(what:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
Function LastCol(sh As Worksheet)
On Error Resume Next
LastCol = sh.Cells.Find(what:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function
采纳答案by A.S.H
Your source ranges are defined as Full Columns. Therefore you cannot paste them anywhere except somewhere at the first row of the destination sheet.
您的源范围定义为Full Columns. 因此，除了目标工作表第一行的某处之外，您不能将它们粘贴到任何地方。
Remember that the number of rows in a worksheet is limited: 1048576rows in Excel 2007 and later versions (65536rows in Excel 2003). Therefore when you try to paste a full column somewhere not in the first row, the copy will exceed the last available row in the destination.
请记住，工作表中的行数是有限的：1048576Excel 2007 和更高版本中的65536行（Excel 2003 中的行）。因此，当您尝试在第一行以外的某处粘贴完整列时，副本将超出目标中的最后一个可用行。
What you can do is take only the used part of the source columns, hoping that there is room for them in the destination sheet. To do this, change the way you define the source range, so that you take only the used part. i.e.:
您可以做的是仅使用源列的已用部分，希望目标表中有空间供它们使用。为此，请更改定义源范围的方式，以便仅获取使用过的部分。IE：
Set CopyRng = sh.UsedRange.Columns("A:G")
' ^^^^^^^^^^^^^^^^^^^
Do the same for all the cases where you set the CopyRng.
对所有设置CopyRng.
Alternatively, you can find the last used row and last used column of the source worksheet the same way you are doing it for the destination worksheet. This option should be more accurate and safer.
或者，您可以按照与目标工作表相同的方式查找源工作表的最后使用的行和最后使用的列。这个选项应该更准确、更安全。
