paste between sheets not working

[Brian.Crawford]

I am trying to paste values between sheets in VBA and the following .PASTE fails but the sample code after it works for different sheets in same workbook (I have removed unrelated code). The error messages are:

"Object doesn't support this property or method"
or: "Pastespecial method of Range class failed" for the pastespecial one
also, .cells.paste and sheets("expadjustments").paste don't work
but the ".CLEARCONTENTS" work in all cases

Also, doing this directly in Excel (ie using "cut & paste") does work but the same code in my VBA fails.
Hmmm.. just went back and tried the routine (farther down) that worked previoulsy and it now fails at the same point

thanks all

With Worksheets("DivisionAdjustments")
.Range("a:bs").ClearContents
.Cells.Copy
End With

With Worksheets("ExpAdjustments")
.Range("a:z").ClearContents
' fails on both the following tests
.Range("a1").Paste
nor does >> .Range("a1").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With

but the following does work for a different set of sheets. The sheets are very raw data, no special fomatting but the failing one does have a 255 character text field (but I deleted most of the characters before attenpting this (as a test))

With Sheets("trialbalance")
.Range("a:az").ClearContents
.Cells.Copy
End With

With Sheets("ExpActuals")
.Range("a:az").ClearContents
' works fine here
.Range("A1").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With

 

[BobUmlas]

Clearcontents will empty the clipboard, so any paste right after a clearcontents will fail. You need to have the Copy active.

 

[Brian.Crawford]

Thanks, I just discoverd that that the clearcontents was the problem, I thought it just cleared the specified cell contents.

Thanks again
Brian