Second time I try to print I get run time error '1004' paste special method of range class failed

Question

I have automated a register using data kept for each register on data sheets which feed via VBA to a 'print out' sheet.

The first run works. When I select another I get

'run time error '1004' paste special method of range class failed'

If I clear the error it will work once and then fail on the second attempt.

Reading an MS help (which I can't find now) it said something like the range is not referenced correctly so it cannot be used until the program has finished. I tried making sure cutcopymode was false. I tried adding an additional copy. PS values using different cells on the sheet and finally tried saving the sheet to see if that cleared the reference.

I have two version, one version recorded and a second one where tried to reference all the ranges (both attached). The macro follows on from a macro that has already selected the data sheet

Sub POP_PRINT_OUT()
'
'   Populate Print Out Sheet with Data from Route Sheet
        
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect
        
    Range("A80:L135").Select
    Selection.Copy
    
    Sheets("Print Out").Select
    ActiveSheet.Unprotect
    
    Sheets("Print Out").Range("A19").Select '# 1004 Error with P.S values below #
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Application.CutCopyMode = False
    Range("A14").Select
    Application.CutCopyMode = False
    'ActiveWorkbook.Save
    
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Application.ScreenUpdating = True
        
End Sub

Sub POP_PRINT_OUTxxxx()
'
'   Populate Print Out Sheet with Data from Route Sheet

Dim sh As Worksheet
Dim rng1 As Range
Dim rng2 As Range
    
Set wb = ThisWorkbook
Set sh = wb.Sheets("Print Out")
Set rng1 = ActiveSheet.Range("A80:L135")
Set rng2 = sh.Range("A19:L74")

    Application.ScreenUpdating = False
    ActiveSheet.Unprotect
        
    rng1.Select
    Selection.Copy
    
    sh.Select
    ActiveSheet.Unprotect
    
    rng2.Select '# 1004 Error with P.S values below #
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Application.CutCopyMode = False
    Range("A14").Select
    Application.CutCopyMode = False
    'ActiveWorkbook.Save
    
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Application.ScreenUpdating = True
        
End Sub

Christofer Weber · Accepted Answer · 2021-09-17T13:23:42.570

0

It would seem that the unprotecting of the sheet is causing problems.
But it's always a good idea to avoid using Select.
Consider this shortened version, change what you need and see if it works better.

...
Set rng2 = sh.Range("A19:L74")

sh.Unprotect
rng1.Copy
rng2.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
sh.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

end sub

If you move the sh.Unprotect in between copy and paste, you can see that the error returns again.

edited Sep 17 '21 at 13:23

answered Sep 17 '21 at 10:40

Christofer Weber

1,464
1
9
18

Thank you it now works. First time I have asked a question and I can't find a way of letting you all know that it has been solved – Chris Fisher Sep 17 '21 at 13:03
I'm glad that it worked. There should be a checkbox under the "Upvote/Downvote" arrows on the answer, that you can use to mark an answer as acceptable, and the question as answered. – Christofer Weber Sep 17 '21 at 13:23

Second time I try to print I get run time error '1004' paste special method of range class failed

1 Answers1