Run-time error '1004': Pate method of worksheet class failed

[ExcelRooky91]

Hi guys,

I'm scratching my head a little bit with this error. The VBA code I have, which pastes a screenshot, is as below. What I'm finding is that it is very temperamental. Some days the code works fine. Other days it comes up with the error as shown in the title. After pasting the screenshot, the next Sub saves and closes the workbook. However, if an error occurs, this ofcourse does not happen.

Is anyone able to elaborate/provide a better solution?

Any help is much appreciated.

Kind regards,
Ty

Sub Pastepic()
Windows("BKBM Workbook.xlsm").Activate
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("a4")


Range("A1:B2").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

 

[NdNoviceHlp]

Re: HELP: Run-time error '1004': Pate method of worksheet class failed

The activesheet is probably messing with U. Not sure what sheet should be...

Windows("BKBM Workbook.xlsm").Activate
Sheets("Sheet1").Paste Destination:=Worksheets("Sheet1").Range("a4")

U can also do this....

Sheets("Sheet1").Range("A1:B2").Copy Destination:= Sheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

Which doesn't seem to make much sense... it copies and pastes to the same cell on the same sheet? Anyways, HTH. Dave

 

[ExcelRooky91]

Re: HELP: Run-time error '1004': Pate method of worksheet class failed

Thanks mate. I changed the code and tried it this morning. All seems to have gone well. Will monitor it over the next couple of days.

 

[NdNoviceHlp]

Re: HELP: Run-time error '1004': Pate method of worksheet class failed

You are welcome. Thanks for posting your outcome. Dave

 

[ExcelRooky91]

Re: HELP: Run-time error '1004': Pate method of worksheet class failed

Hey Dave, no luck unfortunately today. The same error occurred.

 

[NdNoviceHlp]

Re: HELP: Run-time error '1004': Pate method of worksheet class failed

Not sure what's going on but if your using this routine repeatedly, the clipboard will occasionally crash and produce random errors. You can trial this (32 bit install code)..
module code...

Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Public Declare Function EmptyClipboard Lib "user32" () As Long
Public Declare Function CloseClipboard Lib "user32" () As Long

To operate, after pasting....

Application.CutCopyMode = False
OpenClipboard (0&)
EmptyClipboard
CloseClipboard

Good luck. Dave