vba Excel 2010 粘贴值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9384113/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel 2010 paste values
提问by Bob Hausler
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
I am now using this macro which works great. My question is for the workbook "GASP" which has formulas in it. This marco does not copy over the values which I need. Can anyone suggest a work around. Thanks
我现在正在使用这个很好用的宏。我的问题是针对其中包含公式的工作簿“GASP”。这个 marco 不会复制我需要的值。任何人都可以建议解决方法。谢谢
回答by Jerry Beaucaire
First, you only really need this one line once at the top of your macro:
首先，你只需要在宏的顶部这一行：
Set ws = ActiveSheet
Next, to copy VALUES only, you need to switch to a two-line copy/paste command:
接下来，要仅复制 VALUES，您需要切换到两行复制/粘贴命令：
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
回答by Adam Clason
I think what you want to do is have the macro copy the values and not just the formulas. Check the out code below
我认为您想要做的是让宏复制值而不仅仅是公式。检查下面的代码
Set ws = ActiveSheet
Set wbREF = Workbooks.Open("GASP.xls")
Range("B7:L61").Copy
ws.Range("AL7").PasteSpecial xlPasteValues
wbREF.Close False
Also make sure excel has Automatic Calculation turned on. You can check this in the "Formulas" Ribbon. One final thing to be aware of is that you need to be careful about qualifying your references. Whenever you call the "Range" function, it is wise to specify the workbook and the worksheet on which that range is contained. Like this:
还要确保 excel 已打开自动计算。您可以在“公式”功能区中进行检查。最后要注意的一件事是，您需要谨慎地确定您的参考资料。每当您调用“范围”函数时，最好指定包含该范围的工作簿和工作表。像这样：
ThisWorkbook.Sheets("Sheet1").Range("Al7")
or
或者
With ThisWorkbook.Sheets("Sheet1")
Set rng = .Range("Al7")
End With
