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