VBA:粘贴值而不是公式 [英] VBA: Paste Values instead of formulas

问题描述

我是vba的新手.我想使用以下代码将单元格中的某些值从一个选项卡复制到另一个选项卡.

I am new to vba. I want to copy certain values in cells from one tab into another with the following code.

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

这很好，直到我想以某种方式修改代码，以便我的vba代码也可以从公式(例如"= J5 * K24")中复制值(我真的只希望公式返回的值).即使我以以下方式修改了代码，该命令也无法正常工作:

That works fine until I wanted to modify the code in a way so that my vba code can also copy the values ( I really only want the value the formula gives back) from formulas (e.g. "= J5*K24"). That did not work even though 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

有什么想法吗?我读了一些有关PasteSpecial Methode的信息，但目前无法将其应用于我的问题.

Any ideas? I read a bit about the PasteSpecial Methode but could not apply it to my problem at this stage.

谢谢您的帮助！

推荐答案

忘记了PasteSpecial xlValues，直接绕过剪贴板执行直接值传递.

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

您确定最后一个偏移量应该是offset(1，1)而不是offset(1，0)吗?

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

这篇关于VBA:粘贴值而不是公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！