如何在具有双循环的Excel VBA中使用单元格功能摆脱复制和粘贴？

Question

我正在优化一个非常旧的宏代码，我注意到将数据从工作表"源"复制到"测试"，然后再复制到正确的目标工作表"概述"，这是无用的操作。

有没有办法摆脱额外的工作表"测试"和选择，CutCopyMode等？

Dim i As Integer
   Dim m, n As Integer
   Rmin = Application.InputBox("Min row.")
   Rmax = Application.InputBox("Max row.")
   For i = Rmin To Rmax
    For j = 1 To 99
      Sheets("Source").Select
      Cells(i, j).Select
      Application.CutCopyMode = False
      Selection.Copy
      Sheets("Test").Select
      Cells(1, j).Select
      ActiveSheet.Paste
    Next j
      Sheets("Test").Select
      Range("A1:PK1").Select
       Selection.Copy
      Sheets("Overview").Select
      Range("A2").Select
      ActiveSheet.Paste
     Range("A3:I54").Select
     Selection.Copy
    Application.CutCopyMode = False
    Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:Users...." & File & ".pdf" _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False
    Next i
        Sheets("Source").Select
        Cells(Rmax, 1).Select

谢谢！

添加信息：宏用于为工作表"源"中的每个用户选择行创建PDF，然后使用工作表"概述"中的数据表单选择来创建PDF。而且我是整个 VBA 环境的新手，但是，我会尽力而为。我已经在使用 Application.ScreenUpdate 来减少运行时间。

Answer 1

从长远来看，我不完全清楚哪个单元格被复制到哪里，但一般来说，您始终可以使用以下语法避免 .copy/.paste 命令：

  For i = Rmin To Rmax
    For j = 1 To 99
      Sheets("Overview").Cells(2,j).Value = Sheets("Source").Cells(i, j).Value
    Next j

在这种情况下，您可以指定左侧单元格的值等于右侧单元格的内容。

此致敬意瑟尔伯格1

Answer 2

据我所知，您可以轻松地跳过粘贴到测试表的过程。试试这个：

Dim i As Integer
Dim m, n As Integer
Rmin = Application.InputBox("Min row.")
Rmax = Application.InputBox("Max row.")
For i = Rmin To Rmax
  Sheets("Source").Select    'Move to the source sheet
  Range("A" & i & ":PK" & i).Select  'Select the row to copy
  Application.CutCopyMode = False
  Selection.Copy     'Copy the row
  Sheets("Overview").Select  'Move to "Destination"-sheet
  Range("A2").Select   'Paste source into A2 (for some reason)
  ActiveSheet.Paste
  Range("A3:I54").Select  'Select the are you want to create a pdf of
  'And create the PDF...
  Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:Users...." & File & ".pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
Next i  'Move to next row in source sheet
Sheets("Source").Select   'Move back to source
Cells(Rmax, 1).Select     'Select the last cell.

希望这有助于加快速度:)