方法 PasteSpecial 上的 Excel VBA 宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8314007/
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 VBA Macro on the method PasteSpecial
提问by Tresor
I'm working on a macro to concatenate rows coming from different Excel files all located in the same directory Here is the current version:
我正在处理一个宏来连接来自同一目录中的不同 Excel 文件的行这是当前版本：
Sub Compilationb()
Dim Temp As String
Dim Lignea As Long
Temp = Dir(ActiveWorkbook.Path & "\*.xls")
Application.DisplayAlerts = False
Workbooks("RecapB.xls").Sheets(1).Range("A2:Z60000").ClearContents
Do While Temp <> ""
If Temp <> "RecapB.xls" Then
Workbooks.Open ActiveWorkbook.Path & "\" & Tempa
Workbooks(Tempa).Sheets(1).Range("A4").CurrentRegion.Copy
Workbooks("RecapB.xls").Sheets(1).Activate
Lignea = Sheets(1).Range("A65536").End(xlUp).Row + 1
Range("A" & CStr(Lignea)).Select
ActiveSheet.Paste
Workbooks(Temp).Close
End If
Temp = Dir
Loop
Range("A4").Select
Application.DisplayAlerts = True
End Sub
Its working just fine. But the macro copies formulas. And i want it to copy Values instead. So i tried changing the line
它的工作正常。但是宏会复制公式。我希望它改为复制值。所以我尝试改变线路
ActiveSheet.Paste
To
到
ActiveSheet.PasteSpecial xlPasteValues
But its not working. Apparently the method "PasteSpecial" doesnt work on the object "Activesheet". Anyone knows how I can force it to copy values instead ?
但它不起作用。显然，方法“PasteSpecial”不适用于对象“Activesheet”。任何人都知道我如何强制它复制值？
Thanks in advance
提前致谢
回答by GSerg
You need Range.PasteSpecial, not Worksheet.PasteSpecial:
你需要Range.PasteSpecial，而不是Worksheet.PasteSpecial：
ActiveCell.PasteSpecial xlPasteValues
Also, avoid selecting ranges. It is almost never needed. Your routine can be written as:
此外，避免selecting 范围。几乎从不需要它。你的例程可以写成：
Sub Compilationb()
Dim Temp As String
Dim target_sheet As Worksheet
Application.DisplayAlerts = False
Set target_sheet = Workbooks("RecapB.xls").Sheets(1)
target_sheet.Range("A2:Z60000").ClearContents
Temp = Dir(ActiveWorkbook.Path & "\*.xls")
Do While Len(Temp) > 0
If Temp <> "RecapB.xls" Then
Dim current_book As Workbook
Set current_book = Workbooks.Open(ActiveWorkbook.Path & "\" & Temp)
Dim target_range As Range
Set target_range = target_sheet.Cells(target_sheet.Rows.Count, 1).End(xlUp).Offset(1, 0)
current_book.Sheets(1).Range("A4").CurrentRegion.Copy
target_range.PasteSpecial xlPasteValues
Application.CutCopyMode = False
current_book.Close SaveChanges:=False
End If
Temp = Dir
Loop
Range("A4").Select
Application.DisplayAlerts = True
End Sub
