方法 PasteSpecial 上的 Excel VBA 宏 [英] Excel VBA Macro on the method PasteSpecial

问题描述

我正在开发一个宏来连接来自同一目录中的不同 Excel 文件的行这是当前版本:

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:

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

到

ActiveSheet.PasteSpecial xlPasteValues

但它不起作用.显然，方法PasteSpecial"不适用于对象Activesheet".任何人都知道我如何强制它复制值?

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 ?

提前致谢

推荐答案

你需要Range.PasteSpecial，而不是Worksheet.PasteSpecial:

ActiveCell.PasteSpecial xlPasteValues

<小时>

另外，避免select范围.几乎从不需要它.你的例程可以写成:

---

Also, avoid selecting ranges. It is almost never needed. Your routine can be written as:

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

这篇关于方法 PasteSpecial 上的 Excel VBA 宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！