pastespecial in vba

  • Thread starter Thread starter cornishbloke
  • Start date Start date
C

cornishbloke

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.
 
Hi cornishbloke,

how about changing

With Sheets("Quote").Range("A1") to
With Sheets("Quote").cells
(untested)

Best regards

Wolf
 
Hi Wolf,

thanks for the tip. I've tried it but unfortunately it doesn't resolve
the problem.

When I run the macro I get the following warning:

"Pastespecial method of range class failed"
 
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)
 
Thank you Bob, that worked perfectly.

I did already have a separate sub that was setting up the newbook but
have moved this into the same code as you suggest.

Thank you very much and have a happy New Year
 
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 said:
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)
Click to expand...
 
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 said:
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.
Click to expand...
 
Back
Top