Excel 2010 粘贴值

Question

Dim ws As Worksheet, wbREF As Workbook
Set ws = ActiveSheet
Set wbREF = Workbooks.Open("ALSP.xls")      'you may want to put the full path here

Range("B7:L61").Copy ws.Range("N7")         'copy to original sheet
wbREF.Close False

Set ws = ActiveSheet
Set wbREF = Workbooks.Open("FLSP.xls")      'you may want to put the full path here

Range("B7:L61").Copy ws.Range("Z7")         'copy to original sheet
wbREF.Close False

Set ws = ActiveSheet
Set wbREF = Workbooks.Open("GASP.xls")      'you may want to put the full path here

Range("B7:L61").Copy ws.Range("AL7")         'copy to original sheet
wbREF.Close False

我现在正在使用这个宏，效果很好。我的问题是关于工作簿“GASP”，其中有公式。该宏不会复制我需要的值。任何人都可以建议解决方法。谢谢

Answer 1

首先，您实际上只需要在宏顶部出现一次这一行：

Set ws = ActiveSheet

接下来，要仅复制值，您需要切换到两行复制/粘贴命令：

Set wbREF = Workbooks.Open("GASP.xls")      'you may want to put the full path here

Range("B7:L61").Copy 
ws.Range("AL7").PasteSpecial xlPasteValues  'copy to original sheet
wbREF.Close False

Answer 2

我认为你想要做的是让宏复制值而不仅仅是公式。检查下面的代码

Set ws = ActiveSheet
Set wbREF = Workbooks.Open("GASP.xls")

Range("B7:L61").Copy
ws.Range("AL7").PasteSpecial xlPasteValues
wbREF.Close False

同时确保 excel 已打开自动计算。您可以在“公式”功能区中检查这一点。最后要注意的一件事是，您需要小心验证您的推荐人。每当您调用“Range”函数时，明智的做法是指定包含该范围的工作簿和工作表。像这样：

ThisWorkbook.Sheets("Sheet1").Range("Al7")

或者

With ThisWorkbook.Sheets("Sheet1")
     Set rng = .Range("Al7")
End With