Paste Special method of class Failed Error

[iain654]

Application.Run "Violations_VBA.xls!RefreshTable"
Sheets("Total").Select
Sheet1.Range("M55").Copy Destination:=Range("A65536").End(xlUp).Offset(1, 0)
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

The above code brings back a 'PasteSpecial method of Range class failed'. The code is copying from sheet 1 and pasting to the bottom of a list in the Total sheet. What am I doing wrong?

 

[RonRepp]

It looks like you are trying to add a row beyond the total range of 65536.

Sheet1.Range("M55").Copy Destination:=Range("A65536").End(xlUp).Offset(1, 0)

Why not just find the last row and/or last column and then paste?

Check VBA forum for Last Row.

I hope this helps.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[iain654]

The above code does actually work for finding the last row but I am finding very difficult to get it to paste special values the data that I want to put there. It only pastes everything so I end up have formulas in the cells that cause errors. I have added the pastespecial line above but it just gives the error as shown.

 

[SkipVought]

Hi,

I recommend that you avoid using the Select Method. Explicitly reference each object...

Sheet1.Range("M5:P5").Copy 
[b]SomeExplicitlyDefinedRange[/b].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

like this example in VB Help

With [b]Worksheets("Sheet1")[/b]
    .Range("C1:C5").Copy
    .Range("D1:D5").PasteSpecial _
        Operation:=xlPasteSpecialOperationAdd
End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}