VBA Excel Book to Book复制单元格值不是公式 [英] VBA Excel Book to Book copy cell value not formula

问题描述

我已经开发了（借助于stackoverflow用户：@xificurC）Excel中的一个VBA宏，可以在一个单独的工作簿的表格中复制一定范围的单元格并将其粘贴到它们上。但是，这些表上有与我的帐户上的用户权限不可用的数据片段相关的公式。所以我的问题是如何复制单元格的实际面值而不是公式。

I have developed (with help from stackoverflow user: @xificurC ) a VBA macro in excel that copies over a certain range of cells and pastes them, in a table in a seperate workbook. But there are formula on these tables that are relating to pieces of data that are not available with the user permissions on my account. So my question is how can I copy over the actual face values of the cells instead of the formula.

    'get all excel files (and only excel files) from specified folder
    file_checks = Dir(path & "\*.xls*")
     Do Until file_checks = ""
      'open file
       Set wkbklp = Workbooks.Open(path & "\" & file_checks)
       Set wkshtlp = wkbklp.Sheets(1)
      'copy data away from original
       Set data_return = wkshtlp.Range(start_cell).CurrentRegion
       data_return.Copy check_sheet.Cells(check_sheet.Rows.Count, "B").End(xlUp).Offset(1, 0)
      'close file without saving to make it all more automated
       wkbklp.Close False
      'loop through files until all are done
       file_checks = Dir
    Loop

那是传输的代码从一本书到另一本书的数据。我没有找到一个这样做的命令，但我只是业余的，所以可能使用了错误的关键词。

That was the code that transfers the data from one book to another. I have not found a command that does this, but I'm only amateur so may have used the wrong key words.

推荐答案

尝试使用pastespecial。

Try using pastespecial.

   data_return.Copy
   check_sheet.Cells(check_sheet.Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

这篇关于VBA Excel Book to Book复制单元格值不是公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！