Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special won't work in VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special won't work in VBA
There was a problem with xlColumnWidths in Excel 2000. Instead try this:
PasteSpecial Paste:=8 -- Jim Rech Excel MVP |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special won't work in VBA
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. -- Regards, Tom Ogilvy "Jim Rech" wrote in message ... There was a problem with xlColumnWidths in Excel 2000. Instead try this: PasteSpecial Paste:=8 -- Jim Rech Excel MVP |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special won't work in VBA
I tried that, Tom, especially since it worked that way from a right
clickPaste SpecialFormats. 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 "Tom Ogilvy" wrote in message ... 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. -- Regards, Tom Ogilvy "Jim Rech" wrote in message ... There was a problem with xlColumnWidths in Excel 2000. Instead try this: PasteSpecial Paste:=8 -- Jim Rech Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
why don't my functions in paste special work? | Excel Worksheet Functions | |||
Paste special from MS Project to Excel does not always work?? | Excel Discussion (Misc queries) | |||
Paste special into a filtered work sheet | Excel Worksheet Functions | |||
How does the PASTE.SPECIAL function work? | Excel Discussion (Misc queries) | |||
paste special | values should work with merged cells | Excel Discussion (Misc queries) |