VBA粘贴不起作用 [英] vba paste not working

问题描述

因此，到目前为止，我已经生成了此代码，但是无法使粘贴正常工作.

So I have produced this code so far, but I cannot get the paste to work.

这个想法贯穿190个工作簿，并将公式粘贴到某些单元格中，而其他字段中则包含常量(范围H1:Z160)，从而为excel考试打分.如果手动完成，所有的公式和常量都会粘贴并起作用.

The idea is run through 190 workbooks and to paste formulas in some cells with constants in others (range H1:Z160) which grade an excel exam. All the formulas and constants paste and work if done manually.

粘贴功能(标记为)失败，并显示以下错误:

The paste function (labelled) fails with this error:

这是现在已更新并更正的代码:

This is the now updated and corrected code:

    Option Explicit

Sub Examnew()
    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
         wbmaster.Sheets("Answers_Source").Range("h1:z160").Copy
         wbtarget.Sheets("ANSWERS").Range("h1:z160").PasteSpecial

         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
        wbtarget.Sheets("Answers").Range("I4").Copy
        wbmaster.Sheets("studentlist").Range("B" & 2 + i).PasteSpecial xlPasteValues

        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

哪个工作得很好，谢谢大家.

Which works perfectly, Thanks guys.

推荐答案

在该行代码中失败的原因是 Range对象没有粘贴方法.

The reason it fails on that line of code is that there is no Paste method for the Range object.

有两种复制粘贴的方法.

There are 2 ways to copy paste.

1)将值发送到复制"方法中的目标"参数.然后，您不需要粘贴命令: wb.Sheets("Answers_Source").Range("h1:z160").Copy _ Destination := wb2.Sheets("Answers").Range("h1:z160")

1) Send a value to the Destination parameter in the Copy method. You then don't need a Paste command: wb.Sheets("Answers_Source").Range("h1:z160").Copy _ Destination := wb2.Sheets("Answers").Range("h1:z160")

2)复制后在目标范围上使用PasteSpecial方法，默认情况下会粘贴所有内容，例如标准粘贴.

2) Use the PasteSpecial method on the destination range after copying, which by default pastes everything, like a standard paste.

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

然后停止复制的单元格周围的选取框(或行进蚂蚁)，并按Application.CutCopyMode = False

Then to stop the Marquee (or marching ants) around the cell you copied, finish with Application.CutCopyMode = False

这篇关于VBA粘贴不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！