Excel VBA-如何仅插入值? [英] Excel VBA - How to insert values only?

问题描述

我正在寻找一种方法来从其他工作簿复制一个范围(copyrange存在于11列和各种行中)，并且仅在主工作簿中已存在的数据下方插入/粘贴值.

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.

粘贴只是没有问题，但是因为我不希望它覆盖最后(SUM-)行，所以我想插入值.据我所知，没有像InsertSpecial xlInsertValues这样的东西.

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.

那么我该如何基于复制范围内的已计数行插入整个空行，而不是将值仅粘贴到"E"至"O"列中?

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"?

一些前提条件是:

• copyrange来自11列和各种行
• 我试图避免在两个文档之间切换两次.因此，我只想打开extern工作簿，复制值，打开我的主要工作簿，然后在复制的数据中插入范围从左到右的空白单元格.

这是我到目前为止所获得的.在插入部分，这一切都会出错，因为它不仅粘贴/插入值.请注意，这只是更大代码的一部分. Rng31是extern工作簿中的复制范围.

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.

        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

推荐答案

...
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

这篇关于Excel VBA-如何仅插入值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！