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](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?s=1|259.1623). — SJR, Sep 18 '18 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, Sep 18 '18 at 15:26
`xlPasteValues` Here's the list of available options https://learn.microsoft.com/en-us/office/vba/api/excel.xlpastetype — cyboashu, Sep 18 '18 at 15:27
Now I edited my post. I hope that clarifies the problem. Thank you :) — Peter, Sep 18 '18 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, Sep 18 '18 at 15:52

score 4 · Accepted Answer · edited Sep 18 '18 at 15:33

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 '18 at 15:33

dwirony

5,487
3
21
43

answered Sep 18 '18 at 15:30

1

I was wondering the same thing about the (1,1) offset... Seems kind of strange. – dwirony Sep 18 '18 at 15:34
yeah it is strange because I did not show you my whole code. I am sorry about that. – Peter Sep 18 '18 at 15:40
Now I edited the question. I hope that helps. Thank you very much! :) – Peter Sep 18 '18 at 15:47
1

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. – Sep 18 '18 at 15:53
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 – Peter Sep 18 '18 at 15:56
@dwirony I now edited the post back to the normal question. Thank you for your help. For my problem I posted a new question: https://stackoverflow.com/questions/52401097/paste-values-instead-of-formulas-with-pastespecial-vbanewbie I would really appreciate your support. Thank you! – Peter Sep 19 '18 at 08:10

Paste values instead of formulas

1 Answers1

Linked