| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
pastespecial in vba
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/ |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
pastespecial in vba
Hi cornishbloke,
how about changing With Sheets("Quote").Range("A1") to With Sheets("Quote").cells (untested) Best regards Wolf -----Original 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/ . |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
pastespecial in vba
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" --- Message posted from http://www.ExcelForum.com/ |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
pastespecial in vba
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/ |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
pastespecial in vba
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 -- Message posted from http://www.ExcelForum.com |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
pastespecial in vba
It's a pleasure to help a fellow West Country man.
Regards Bob "cornishbloke" wrote in message ... Thank you Bob, that worked perfectly. I did already have a separate sub that was setting up the newbook but I have moved this into the same code as you suggest. Thank you very much and have a happy New Year! --- Message posted from http://www.ExcelForum.com/ |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
pastespecial in vba
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/ |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
pastespecial in vba
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/ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VB PasteSpecial when range changes | Excel Programming | |||
| vba pastespecial | Excel Programming | |||
| PasteSpecial:VBA vs right click | Excel Programming | |||
| PasteSpecial failed | Excel Programming | |||
| pastespecial | Excel Programming | |||
![cornishbloke[_13_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
