Excel VBA - 如何仅插入值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18020670/
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
Excel VBA - How to insert values only?
提问by VBA Newbe
I'm looking for a way to copy a range (copyrange exists from 11 columns and a variety of rows) from an other workbook and insert/paste values only in my primary workbook, underneath the data already there.
我正在寻找一种方法来从其他工作簿复制范围（复制范围存在于 11 列和各种行中），并仅在我的主工作簿中已存在的数据下方插入/粘贴值。
Just pasting is no issue, but because I don't want it to overwrite the last (SUM-)line I want to INSERT the values. By my knowing there isn't anything like InsertSpecial xlInsertValues.
只是粘贴没问题，但因为我不希望它覆盖最后一行（SUM-），所以我想插入值。据我所知，没有像 InsertSpecial xlInsertValues 这样的东西。
So how can I insert entire empty rows based on the counted rows of the copied range and than paste the values only in columns "E" to "O"?
那么如何根据复制范围的计数行插入整个空行，而不是仅将“E”列中的值粘贴到“O”？
Some preconditions are:
一些先决条件是：
- copyrange exist from 11 columns and a variety of rows
- I'm trying to avoid having to switch twice between the two documents. So I only want to open the extern workbook, copy the values, open my primary workbook en insert the copied data with blank cells left and right from the range.
- copyrange 存在于 11 列和各种行中
- 我试图避免在两个文档之间切换两次。所以我只想打开外部工作簿，复制值，打开我的主工作簿，然后插入复制的数据，并从范围的左侧和右侧插入空白单元格。
This is what I've got so far. It all goes wrong at the Insert part, because it doesn't paste/insert values only. Note that it's only a part of a bigger code. Rng31 is the copied range in the extern workbook.
这是我到目前为止所得到的。在插入部分这一切都出错了，因为它不仅仅粘贴/插入值。请注意，它只是更大代码的一部分。Rng31 是 extern 工作簿中的复制范围。
Dim Rng31 As Range
Set Rng31 = Rng21.Resize(Rng21.Rows.Count, Rng21.Columns.Count + 10)
Dim regels As Integer
regels = Rng31.Rows.Count
Rng31.Copy
Wbbase.Activate
Sheets(2).Activate
Dim onderste As Range
Set onderste = Range("E65536").End(xlUp).Offset(1, 0)
onderste.Insert shift:=xlDown
Selection.PasteSpecial xlPasteValues
回答by Tim Williams
...
InsertValues Rng21.Resize(Rng21.Rows.Count, Rng21.Columns.Count + 10), _
Wbbase.Sheets(2).Range("E65536").End(xlUp).Offset(1, 0)
...
Sub InsertValues(rngCopyFrom As Range, rngCopyTo As Range)
Dim rngDest As Range
Set rngDest = rngCopyTo.Resize(rngCopyFrom.Rows.Count, _
rngCopyFrom.Columns.Count)
Application.CutCopyMode = False 'Edit Added:clear any copied data
rngDest.Insert shift:=xlDown
rngDest.Offset(-rngCopyFrom.Rows.Count).Value = rngCopyFrom.Value
End Sub
回答by chancea
The problem is that if you have a cell copied (i.e. its on the clipboard) you cannot simply "insert a new row"because the only option is to "insert copied cells"thus the onderste.Insertwill insert the copied cell and keep the formula. In addition you then call Selection.PasteSpecialwhich I do not think is what you desired because you never set your selection anywhere in your code post, so whatever you had selected prior is what will contain the value.
问题是，如果您复制了一个单元格（即它在剪贴板上），您不能简单地“插入新行”，因为唯一的选择是“插入复制的单元格”，因此onderste.Insert将插入复制的单元格并保留公式。此外，您然后调用Selection.PasteSpecial它，我认为这不是您想要的，因为您从未在代码帖子中的任何位置设置您的选择，因此您之前选择的任何内容都将包含该值。
You have a couple options:
您有几个选择：
- You can insert the row first, then copy and paste the cell value (this requires toggling back and forth between workbooks)
- Copy the cell, insert the new row (which is what you are doing) then clear the row and paste in the values.
- 您可以先插入行，然后复制并粘贴单元格值（这需要在工作簿之间来回切换）
- 复制单元格，插入新行（这是您正在执行的操作），然后清除该行并粘贴值。
As a side note:
作为旁注：
Range("E65536").End(xlUp).Offset(1, 0)
Range("E65536").End(xlUp).Offset(1, 0)
would be better stated as
会更好地表述为
Range("E" & Rows.Count).End(xlUp).Offset(1, 0)
Range("E" & Rows.Count).End(xlUp).Offset(1, 0)
