Paste Special won't work in VBA

[Ed]

Once again, I have run into something I don't understand. I copy an entire
spreadsheet, go to a blank sheet, and Paste Special for values, column
widths, and formats. I used the macro recorder to get the syntax for the
Paste Special events.



The Column Widths, though, won't work in VBA. Values and Formats do, but
Formats won't do the Column Widths in VBA. On a right click, though, I can
do both Formats - which will include Column Widths - and just Column Widths.



The beginning of the code which includes the Paste Specials follows. Any
help is appreciated.



Ed



Sub SlimFile()

'

' SlimFile Macro

' Macro recorded 8/15/2003 by Authorized User



' Turn off alerts

Application.DisplayAlerts = False



' Add new worksheet

Sheets.Add



' Copy info from Sheet1

Sheets("Sheet1").Select

Cells.Select

Selection.Copy



' Paste values, formats to Sheet 2

Sheets("Sheet2").Select

Range("A1").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_

False, Transpose:=False

Range("A1").Select

Selection.PasteSpecial Paste:=xlColumnWidths, Operation:=xlNone, _

SkipBlanks:=False, Transpose:=False

Range("A1").Select

Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:=
_

False, Transpose:=False

Range("A1").Select

 

[Jim Rech]

There was a problem with xlColumnWidths in Excel 2000. Instead try this:

PasteSpecial Paste:=8

 

[Tom Ogilvy]

True, but to add a thought:

Cells.Copy

then
Range("A1").PasteSpecial xlFormats

should paste column widths and row heights as well since entire columns/rows
are being pasted.

so pasting column widths should be redundant.

 

[Ed]

I tried that, Tom, especially since it worked that way from a right
click>Paste Special>Formats. But in VBA, even as recorded, I got formats
but not the column widths. That's why I put it in separately. Then I kept
getting "Paste Special method of range class failed" errors.

The Paste:=8 worked for widths. But I had pasted Values first, and it
overwrote my values! Okay, so I change things around and do the values
last. *sigh*

Ed