It could be lots of things.

What line causes the error--you have lots of .pastespecial lines.

Do you have any worksheet/workbook events that are running when you change
sheets or change selection? Maybe running that macro is killing the clipboard.

Maybe you could drop the .select's:

Option Explicit
Sub testme()

Dim RngToCopy As Range
Dim DestCell As Range

Worksheets("Sales").Range("Sales_Table").Clear

With Worksheets("Sales Freq").Range("Sales_Table")
Set RngToCopy = .Resize(.Rows.Count, .Columns.Count + 3)
End With

Set DestCell = Worksheets("Sales").Range("A4")

RngToCopy.Copy

With DestCell
.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With

Application.CutCopyMode = False

End Sub


If that doesn't work, are you running the macro using the same version of excel
that created the macro?

"Sam via OfficeKB.com" wrote:

Hi All,

I would like to copy a named range from one worksheet to another within the
same workbook; the range contains constants, formulas and specific formatting
(number formats and column widths).

I'm trying to copy range("Sales_Table") from worksheet Sales Freq to range
("A4") of worksheet Sales.

I 've tried using the code below but get Microsoft VB error message, Run-time
error 1004:
PasteSpecial method of Range class failed.

Sheets("Sales").Activate
Range("Sales_Table").Clear

Sheets("Sales Freq").Activate
Range("Sales_Table").Select
Selection.Resize(Selection.Rows.count, _
Selection.Columns.count + 3).Select
Selection.Copy

Sheets("Sales").Activate
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=
_
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Help very much appreciated.

Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200803/1

--

Dave Peterson