pastespecial in vba

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.
 
W

Wolf

Hi cornishbloke,

how about changing

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

Best regards

Wolf
 
C

cornishbloke

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"
 
B

Bob Phillips

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)
 
C

cornishbloke

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
 
T

Tom Ogilvy

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...
 
B

Bob Phillips

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with condensing a lot of copying/pasting... 4
Run-time error 1004 paste method of worksheet class failed 3
conso macro 0
PasteSpecial problem 8
Would like a more efficient way to Copy/PasteSpecial 4
Run-time error '1004': Methor 'Range' of object '_Global' failed 3
Paste Special Macro 4
Copy and Paste Charts as Picture in Different Workbook 12

Top