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/