Search code examples
excelvbaruntime-errorexcel-2010

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


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

Solution

  • 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.