VBA 宏未正确粘贴
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26982114/
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 Macro not pasting correctly
提问by JaReg
I created a VB macro that combines data from 2 sheets into a single sheet so that it can be printed and given out as an overview of the projects we are working on.
我创建了一个 VB 宏，它将 2 张工作表中的数据合并到一张工作表中，以便可以将其打印出来并作为我们正在处理的项目的概述。
What it should (And did in the past) is to go to Sheet 1, copy all the data from A3 to R of the last row that had data in column A.
它应该做的（过去做过）是转到第 1 页，将 A3 中的所有数据复制到 A 列中有数据的最后一行的 R。
It then pastes that data starting in A3 of the combined worksheets.
然后从组合工作表的 A3 开始粘贴该数据。
Then it copies the data the same way from Worksheet 2 and pastes it after the last used row in the combined worksheet.
然后它以相同的方式从工作表 2 复制数据，并将其粘贴到组合工作表中最后使用的行之后。
I made some adjustments recently and now the macro doesn't seem to be working.
我最近做了一些调整，现在宏似乎不起作用。
It pastes the first worksheet correctly (Lean Projects), but the second worksheet's data (Kaizen) isn't copying correctly.
它正确粘贴了第一个工作表（精益项目），但第二个工作表的数据（Kaizen）没有正确复制。
Instead of copying all the data down to the final input row, it copies all the data starting after the number of rows in the first worksheet. (Example: if there are 24 entries in worksheet 1, worksheet 2 is beginning to copy at row 25.
它不是将所有数据向下复制到最后的输入行，而是从第一个工作表中的行数之后开始复制所有数据。（例如：如果工作表 1 中有 24 个条目，则工作表 2 从第 25 行开始复制。
Sub CreateCombinedSheet()
'Assign the worksheets to their respective variables
Set wsCombined = Sheets("Combined (View)(Macro)")
Set wsProjects = Sheets("Lean Projects (View)")
Set wsKaizen = Sheets("Kaizen (View)")
'Clear the Combined worksheet before repopulating it if there is data present
'If the first cell of data, A3, is not empty
If wsCombined.Range("A3") <> "" Then
'Then clear all rows after row 3 until the last row
wsCombined.Range("A3", wsCombined.Range("A1048576").End(xlUp).Address).EntireRow.Delete
End If
'Copy all the data in the Lean Projects worksheet
wsProjects.Range("A3", wsProjects.Range("R3").End(xlDown).Address).Copy
'Paste the Lean Projects data into the Combined worksheet
wsCombined.Range("A3").PasteSpecial
'Copy all the data in the Kaizen worksheet
wsKaizen.Range("A3", wsKaizen.Range("R3").End(xlDown).Address).Copy
'Paste the Kaizen data into the Combined worksheet starting in the row after the currently last used row
wsCombined.Range("A" & wsCombined.Range("A" & Rows.Count).End(xlUp).Row + 1).PasteSpecial
End Sub
If anyone can tell where I went wrong, I'd appreciate the help.
如果有人能告诉我哪里出错了，我将不胜感激。
Thanks,
谢谢，
Example: 
例子：
Update 11/18:
11/18 更新：
I commented out the Second Paste function so I could see for myself exactly what was being copied. I found that it is copying the correct data from the second worksheet. The issue comes when I paste that data after the last row in the combined worksheet (After the first sheet's data ends). When I manually past there, I see all the second sheet data as it should be. Then a second later, it appears to collapse upward and Im left with the data starting at row 25 of the second sheet to the last row (28) then the formula on sheet 2 is extrapolated further up to row 50 on the combined sheet.
我注释掉了 Second Paste 函数，这样我就可以亲眼看到正在复制的内容。我发现它正在从第二个工作表中复制正确的数据。当我将该数据粘贴到组合工作表的最后一行之后（在第一个工作表的数据结束之后）时，问题就出现了。当我手动通过那里时，我会看到所有的第二张数据。然后一秒钟后，它似乎向上折叠，我留下了从第二张工作表的第 25 行开始到最后一行 (28) 的数据，然后将工作表 2 上的公式进一步外推到组合工作表上的第 50 行。
采纳答案by Chrismas007
Try adding a xlPasteValuesafter each of your .PasteSpecialcode lines
尝试xlPasteValues在每个.PasteSpecial代码行之后添加
wsCombined.Range("A3").PasteSpecial xlPasteValues
And
和
wsCombined.Range("A" & wsCombined.Range("A" & .Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteValues
回答by cronos2546
Corrections (in my opinion)
更正（在我看来）
If wsCombined.Range("A3") > 0 Then
Should become
应该成为
IF wsCombined.Range("A3").Value <> "" Then
Don't use a custom function for the End function. It's redundant, and limits what you can do with it.
不要为 End 函数使用自定义函数。它是多余的，并且限制了你可以用它做什么。
wsCombined.Rows("3:" & LastRow(wsCombined)).ClearContents
Should become
应该成为
wsCombined.Range("A3",WSCombined.Range("A1048576").End(xlup).Address).EntireRow.Delete
Another
其他
wsProjects.Range("A3:" & "R" & LastRow(wsProjects)).Copy
Should become
应该成为
wsProjects.Range("A3",wSProject.Range("R3").End(xlDown).Address).Copy
And so and so forth. I will circle back to this answer shortly, but I would imagine that if you remove your custom function and use the built in functionality for this, the error will go away.
等等等等。我很快就会回到这个答案，但我想如果您删除自定义函数并为此使用内置功能，错误就会消失。
