vba paste not working

Question

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

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.

score 2 · Accepted Answer · answered Jun 09 '18 at 16:23

2

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) 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) 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

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

answered Jun 09 '18 at 16:23

John F

276
1
6

Method one did not want to play? But method 2 works fine. – Solar Mike Jun 09 '18 at 17:07
Glad (2) worked. For (1) it might be the way I typed it in the edit box. A simplified example of (1) is `Range("A1").Copy Destination:=Range("B1")` – John F Jun 09 '18 at 17:19

score 2 · Answer 2 · answered Jun 04 '19 at 15:29

Even though this has been answered, the Range Value property is something that should be included as an option for this question.

If you're only looking to CopyPasteValues, it is probably better to adjust the Range Value Property to be equal to the Source Range Values.

A couple advantages:

No marching ants (Application.CutCopyMode = False).
The screen should not need to flash update/scroll.
Should be faster.
You don't even need to unhide or activate (which you don't with Copying, but people think you do... so I'm listing it!).

So I rebuilt your Macro with the changes, though I didn't make any other changes, so whatever else you fixed, would probably need to be done again. I also included a second macro (TimerMacro) that you can use to time how long it runs (in case you want to test the performance differences). If you're not using any dates, you can use the property Value2 for a very slight speed improvement, although I haven't seen much improvement with this. Good Luck!

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

score 1 · Answer 3 · answered Jun 09 '18 at 15:59

1

Try removing these With which anyway make no sense in the context.

   '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

answered Jun 09 '18 at 15:59

iDevlop

24,841
11
90
149

Thanks for that : was being too controlling I think. – Solar Mike Jun 09 '18 at 17:01

score 0 · Answer 4 · answered Jun 09 '18 at 16:07

Try going to visual basic editor -> tools -> reference. Check the reference that you are using and see if you active all the reference you need. The root cause of this appears to be related to problems mentioned in https://support.microsoft.com/en-ph/help/3025036/cannot-insert-object-error-in-an-activex-custom-office-solution-after and https://blogs.technet.microsoft.com/the_microsoft_excel_support_team_blog/2014/12/

score 0 · Answer 5 · edited May 01 '23 at 12:35

0

After looking for almost one hour for questions involving "exportasfixed failed" and not having any solution, I finally found one code that worked for me:

Sub OneClickPDF()
'
' PDF3 Macro
'
'
     Dim saveName As String
     saveName = ActiveDocument.Name
     saveName = Left(saveName, Len(saveName) - 5) & ".pdf"
     saveName = "/Users/BMac/MyDocs/" & saveName
     ActiveDocument.SaveAs FileName:=saveName, FileFormat:=wdFormatPDF
End Sub

edited May 01 '23 at 12:35

Suraj Rao

29,388
11
94
103

answered May 01 '23 at 12:32

user21787224

1

Welcome to SO and thank you for contributing. You might want to reference the source of this code to ensure due credit is given. Failing to do so might result in deletion of the answer for plagiarism. – mozway May 01 '23 at 12:52
As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 02 '23 at 20:58

vba paste not working

5 Answers5

Linked