Paste values instead of formulas

Question

I want to copy certain values in cells from one tab into another.

    Sheets("Equities").Select
    Range("B5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("ZSM").Select
    Range("B5").Select
    ActiveSheet.Paste
    Sheets("Bonds").Select
    Range("B5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("ZSM").Select
    Range("B5").Select
    Selection.End(xlDown).Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(1, 1).Select
    ActiveSheet.Paste

I want to modify the code to also copy the values (I only want the value the formula gives back) from formulas (e.g. "= J5*K24").

I modified the code the following way:

    Sheets("Equities").Select
    Range("B5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("ZSM").Select
    Range("B5").Select
    ActiveSheet.PasteSpecial               ###here
    Sheets("Bonds").Select
    Range("B5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("ZSM").Select
    Range("B5").Select
    Selection.End(xlDown).Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(1, 1).Select
    ActiveSheet.PasteSpecial                  ##here

I read a bit about the PasteSpecial method but could not apply it.

Not an answer to your question, but please please please read How to avoid Select in VBA. — SJR
– SJR, Commented Sep 18, 2018 at 15:24
For example, you can use Sheets("Equities").Range("B5").Copy // Sheets("ZSM").Range("B5").PasteSpecial xlValues or even quicker the one-line Sheets("ZSM").Range("B5").value=Sheets("Equities").Range("B5").value. — SJR
– SJR, Commented Sep 18, 2018 at 15:26
xlPasteValues Here's the list of available options learn.microsoft.com/en-us/office/vba/api/excel.xlpastetype — cyboashu
– cyboashu, Commented Sep 18, 2018 at 15:27
Now I edited my post. I hope that clarifies the problem. Thank you :) — Peter
– Peter, Commented Sep 18, 2018 at 15:48
SJR's initial comment cannot be overstated. Please avoid the use of Select in VBA. It can lead to weird behaviour that is not easy to debug. — Taelsin
– Taelsin, Commented Sep 18, 2018 at 15:52

dwirony · Accepted Answer · 2018-09-18 15:33:49Z

4

Forget the PasteSpecial xlValues and perform a direct value transfer hereby bypassing the clipboard altogether.

dim zsm as worksheet

set zsm = workSheets("ZSM")

with workSheets("Equities")
    with .Range(.range(.cells(5, "B"), .cells(.rows.count, "B").end(xlup)), _
                .range(.cells(5, "B"), .cells(5, .columns.count).end(xltoleft)))
        zsm.cells(5, "B").resize(.rows.count, .columns.count) = .value
    end with
end with

with workSheets("Bonds")
    with .Range(.range(.cells(5, "B"), .cells(.rows.count, "B").end(xlup)), _
                .range(.cells(5, "B"), .cells(5, .columns.count).end(xltoleft)))
        zsm.cells(zsm.rows.count, "B").end(xlup).offset(1, 1).resize(.rows.count, .columns.count) = .value
    end with
end with

Are you sure that last offset should be offset(1, 1) and not offset(1, 0)?

edited Sep 18, 2018 at 15:33

dwirony

5,4604 gold badges26 silver badges49 bronze badges

answered Sep 18, 2018 at 15:30

user4039065

Sign up to request clarification or add additional context in comments.

6 Comments

dwirony Over a year ago

I was wondering the same thing about the (1,1) offset... Seems kind of strange.

Peter Over a year ago

yeah it is strange because I did not show you my whole code. I am sorry about that.

Peter Over a year ago

Now I edited the question. I hope that helps. Thank you very much! :)

user4039065 Over a year ago

Now you're just overwriting almost all the data in ZSM you got from Equities with the values from Bonds. In addition, you've rendered this response completely invalid.

Peter Over a year ago

Yeah thats what this response does. I wanted to show you why I have to use offset(1,1). As I said, I am really knew to this and do not know how I have to behave in stackoverflow. I apologize for the inconveniences. Hope that someone can hepl

|

Collectives™ on Stack Overflow

Paste values instead of formulas

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related