Tom,

As ever., thanks. I didn't know that, just looked at the help (that will
teach me<vbg). It's a bit perverse though to put it mildly,

Regards

Bob

"Tom Ogilvy" wrote in message
...
In xl2000, if you record a macro and do paste special column widths it
records the constant xlColumnWidths

Selection.PasteSpecial Paste:=xlColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

However, this constant was not defined in xl2000 and the hard coded value
8
should be used.

Selection.PasteSpecial Paste:=8, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


This is just for information. There is no reason to do this as a separate
operation in the current situation.

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
Cornish,

A number of problems.

xlColumnWidths is not a pastespecial option, it is part of xlFormats
when referencing within a With decl;aration,. you need to dot qualify
the
methods
you didn't seem to setup a NewBook object, or name Quote and Cost
Summary
within them
you can paste a picture to a sheet,. not a cell as it is on a layer
above
the sheet so to speak


Try this

Sub test()
Dim NewBook As Workbook

Call OpenBook
Set NewBook = Workbooks.Add
With NewBook
.Worksheets(1).Name = "Quote"
.Worksheets(2).Name = "Cost Summary"
End With

Workbooks("Quote Template.xls").Worksheets("Quote").Cells.Copy
With NewBook.Worksheets("Quote").Range("A1")
.PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With

Workbooks("Quote Template.xls").Worksheets("Cost
Summary").Cells.Copy
With NewBook.Worksheets("Cost Summary").Range("A1")
.PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With

Workbooks("Quote Template.xls").ActiveSheet.Shapes("Picture 1").Copy
NewBook.ActiveSheet.Paste

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"cornishbloke" wrote in
message
...
Try and try as I might I can't get pastespecial to work in the
following
code.

This code does (or rather I would like it to do) the following:
1) opens a new workbook (newbook)
2) copies the entire of the "Quote" sheet in the "Quote Template"
3) attempts to paste this sheet to a sheet of the same name in the
newbook (I need it to copy the column widths, formatting and values)
4) it then repeats the above for a sheet called "Cost Summary"
5) it copies a logo and pastes it in the newbook

Sub test()
'
'
Call OpenBook
Windows("Quote Template.xls").Activate
Sheets("Quote").Select
Cells.Copy
NewBook.Activate
With Sheets("Quote").Range("A1")
PasteSpecial Paste:=xlColumnWidths, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False 'the code fails at this point
PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
End With
Application.CutCopyMode = False
Range("B2").Select
Windows("Quote Template.xls").Activate
Sheets("Cost Summary").Select
Cells.Copy
NewBook.Activate
With Sheets("Cost Summary").Range("A1")
PasteSpecial Paste:=xlColumnWidths, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
End With
Application.CutCopyMode = False
Windows("Quote Template.xls").Activate
ActiveSheet.Shapes("Picture 1").Copy
Range("C41").Select
NewBook.Activate
Range("C1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Windows("Quote Template.xls").Activate
End Sub

I've read previous posts about pastespecial although I still don't
understand what I'm doing wrong. My coding is amateur as can no doubt
be seen from my inefficient code above - please explain in as simple
terms as possible what I might be doing wrong.


---
Message posted from http://www.ExcelForum.com/