CODEWITHSUNDEEP
excelvbainsert
VBA Newbe
VBA Newbe

Reputation: 21

Excel VBA - How to insert values only?

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.

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.

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

Some preconditions are:

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

Upvotes: 2

Views: 15703

Answers (2)

Tim Williams
Tim Williams

Reputation: 166970

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

Upvotes: 2

chancea
chancea

Reputation: 5968

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.Insert will insert the copied cell and keep the formula. In addition you then call Selection.PasteSpecial which 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.

You have a couple options:

  1. You can insert the row first, then copy and paste the cell value (this requires toggling back and forth between workbooks)
  2. 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)

would be better stated as

Range("E" & Rows.Count).End(xlUp).Offset(1, 0)

Upvotes: 0

Related Questions