VBA粘贴不工作

它在那行代码上失败的原因是 范围对象没有粘贴方法 .

复制粘贴有两种方法。

1)向复制方法中的目标参数发送一个值。这样就不需要粘贴命令: wb.Sheets("Answers_Source").Range("h1:z160").Copy _ Destination := wb2.Sheets("Answers").Range("h1:z160")

2)复制后对目标区域使用PasteSpecial方法,默认情况下,该方法会像标准粘贴一样粘贴所有内容。

wb2.Sheets("Answers").Range("h1:z160").PasteSpecial

然后停止在你复制的单元格周围使用字幕(或蚂蚁行军),用 Application.CutCopyMode = False

尝试移除这些 With 无论如何,这在上下文中没有意义。

   'do copy from reference "Answers_Source" worksheet
   wb.Sheets("Answers_Source").Range("h1:z160").Copy

   'now paste the formulas into the student exam workbook
   wb2.Sheets("Answers").Range("h1:z160").Paste      

即使这已经得到了回答, Range Value property 是应该包括在这个问题中的选项。

如果你只想 CopyPasteValues ,调整范围可能更好 Value 属性等于源范围值。

几个优点:

• 没有行军的蚂蚁( Application.CutCopyMode = False )。
• 屏幕不需要闪存更新/滚动。
• 应该更快。
• 您甚至不需要取消隐藏或激活(复制时不需要,但人们认为您需要…所以我列出了它!).

所以我用这些更改重新构建了宏,虽然我没有做任何其他更改,所以无论您修复了什么,都可能需要再次执行。我还包括第二个宏(timermacro),您可以使用它来计算运行时间(以防您想测试性能差异)。如果不使用任何日期,则可以使用该属性 Value2 for a very slight speed improvement 尽管我没有看到这方面有什么改进。 祝你好运!

Sub Examnew_NEW()
    Dim rCell As Range, rRng As Range 'define loop names
    Dim wbmaster As Workbook                     'name for master workbook
    Dim wbtarget As Workbook                      'name for student workbook
   Set wbmaster = ActiveWorkbook               'set the name for the master
   Dim i As Long                                           'a counter for the result pasteback

With Application '<--|turn off screen & alerts only removed while testing
.ScreenUpdating = False
.EnableEvents = False
End With

i = 1   'Set the counter for result paste back

    'Student numbers in cells B3:B136 WARNING SET TO 2 STUDENTS ONLY FOR TEST
    'NOTE that st Nums are in col B with a duplicate in col A to collect results.
    Set rRng = wbmaster.Sheets("studentlist").Range("B3:B4")
    ActiveSheet.DisplayPageBreaks = False '<  | turn off page breaks for speed

    For Each rCell In rRng '<                 | loop through "students" range

         ActiveSheet.DisplayPageBreaks = False '<  | turn off page breaks for speed

      'now open Student exam workbook and set to name "wbtarget"
         Workbooks.Open ("/Users/michael/Final_V1/" & rCell.Value & ".xlsx")
         Set wbtarget = Workbooks(rCell.Value & ".xlsx")

     'do copy & paste from Master to Target
     'PGCodeRider CHANGED!!!!!!!!!!!!!!
     wbtarget.Sheets("ANSWERS").Range("h1:z160").Value = _
         wbmaster.Sheets("Answers_Source").Range("h1:z160").Value


         Application.CutCopyMode = False      'Clear the copy command

    'Now collect the result in cell I4 and paste it back into column B using the rCell
    'for that student number matches the st num in col A


        'PGCodeRider CHANGED!!!!!!!!!!!!!!
        wbmaster.Sheets("studentlist").Range("B" & 2 + i).Value = _
            wbtarget.Sheets("Answers").Range("I4").Value

        Application.CutCopyMode = False      'Clear the copy command

     'now save and close the student file...
        wbtarget.Close (True)

        i = i + 1      'increment i for next pasteback


    Next rCell   '<                            | next student number
   'save the results file
   wbmaster.Save


       ActiveSheet.DisplayPageBreaks = True '<    | turn back on page breaks once all done

'turn screen & alerts back on
With Application
.ScreenUpdating = True: .DisplayAlerts = True
'.DisplayPageBreaks = True
End With
End Sub


Sub timerMACRO()
'Run this if you want to run your macro and then get a timed result
Dim beginTime As Date: beginTime = Now

Call Examnew_NEW

MsgBox DateDiff("S", beginTime, Now) & " seconds."

End Sub

尝试转到Visual Basic编辑器->工具->参考。检查您正在使用的引用,看看您是否激活了所有需要的引用。其根本原因似乎与 https://support.microsoft.com/en-ph/help/3025036/cannot-insert-object-error-in-an-activex-custom-office-solution-after 和 https://blogs.technet.microsoft.com/the_microsoft_excel_support_team_blog/2014/12/