Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 97: Copy Range with all formatting to another Sheet
Hello all,
just want to copy the Range from one Sheet to another one, values and all formats, perfect would be even the column width. When I copy and paste manually, all is working perfect, but not with VBA. Be aware that there are joined cells in my sheet. Any hints? Thanks and greetings Udo |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 97: Copy Range with all formatting to another Sheet
Sub test2()
Dim rFrom As Range, rTo As Range Set rFrom = ActiveWorkbook.Worksheets("Sheet1").Range("A1:B10" ) Set rTo = ActiveWorkbook.Worksheets("Sheet2").Range("C1") rFrom.Copy rTo End Sub This method will not copy row/column dimensions. I don't know what you mean by "joined cells". Regards, Peter T "Udo" wrote in message ... Hello all, just want to copy the Range from one Sheet to another one, values and all formats, perfect would be even the column width. When I copy and paste manually, all is working perfect, but not with VBA. Be aware that there are joined cells in my sheet. Any hints? Thanks and greetings Udo |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 97: Copy Range with all formatting to another Sheet
Udo -
As I recall, in Excel 97, I had to use three separate Copy and Paste commands: one for cell values, one for cell formats, and one for column widths. If by "joined cells" you mean merged cells, I would avoid the many problems associated with merged cells by redesigning the worksheet so they are not needed. Often, the horizontal format "center across selection" is an alternative. - Mike Middleton http://www.DecisionToolworks.com Decision Analysis Add-ins for Excel "Udo" wrote in message ... Hello all, just want to copy the Range from one Sheet to another one, values and all formats, perfect would be even the column width. When I copy and paste manually, all is working perfect, but not with VBA. Be aware that there are joined cells in my sheet. Any hints? Thanks and greetings Udo |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 97: Copy Range with all formatting to another Sheet
"Mike Middleton" wrote in message
As I recall, in Excel 97, I had to use three separate Copy and Paste commands: one for cell values, one for cell formats, and one for column widths. I'll stand corrected but I don't think Excel 97 has any facility to paste column widths unless an entire sheet is copied to a new sheet. There is of course PasteSpecial that can paste values or formats and some other things, but not row/column dim's. Regards, Peter T |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 97: Copy Range with all formatting to another Sheet
I think xl2k added:
range.pastespecial paste:=xlPasteColumnWidths And it even had a bug. MS didn't create that constant--except in the help. In xl2k, you would have to use: range.pastespecial paste:=8 Peter T wrote: "Mike Middleton" wrote in message As I recall, in Excel 97, I had to use three separate Copy and Paste commands: one for cell values, one for cell formats, and one for column widths. I'll stand corrected but I don't think Excel 97 has any facility to paste column widths unless an entire sheet is copied to a new sheet. There is of course PasteSpecial that can paste values or formats and some other things, but not row/column dim's. Regards, Peter T -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 97: Copy Range with all formatting to another Sheet
Indeed it was introduced in XL2000 but it's not even documented in XL2000's
help at all "Paste Optional Variant. The part of the range to be pasted. Can be one of the following XlPasteType constants: xlPasteAll, xlPasteFormulas, xlPasteValues, xlPasteFormats, xlPasteNotes, or xlPasteAllExceptBorders. The default value is xlPasteAll." This works in xl2k but fails in xl97 range.pastespecial paste:=xlPasteColumnWidths However, in XL97 if entire columns are copied then Pastespecial formats will paste column widths. That requires of course that the destination is one or same number of columns as the source. Regards, Peter T "Dave Peterson" wrote in message ... I think xl2k added: range.pastespecial paste:=xlPasteColumnWidths And it even had a bug. MS didn't create that constant--except in the help. In xl2k, you would have to use: range.pastespecial paste:=8 Peter T wrote: "Mike Middleton" wrote in message As I recall, in Excel 97, I had to use three separate Copy and Paste commands: one for cell values, one for cell formats, and one for column widths. I'll stand corrected but I don't think Excel 97 has any facility to paste column widths unless an entire sheet is copied to a new sheet. There is of course PasteSpecial that can paste values or formats and some other things, but not row/column dim's. Regards, Peter T -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 97: Copy Range with all formatting to another Sheet
Weird, in XL2k a recorded macro returns -
Selection.PasteSpecial Paste:=xlColumnWidths ' etc But it fails on playback as the named constant is not found Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... Indeed it was introduced in XL2000 but it's not even documented in XL2000's help at all "Paste Optional Variant. The part of the range to be pasted. Can be one of the following XlPasteType constants: xlPasteAll, xlPasteFormulas, xlPasteValues, xlPasteFormats, xlPasteNotes, or xlPasteAllExceptBorders. The default value is xlPasteAll." This works in xl2k but fails in xl97 range.pastespecial paste:=xlPasteColumnWidths However, in XL97 if entire columns are copied then Pastespecial formats will paste column widths. That requires of course that the destination is one or same number of columns as the source. Regards, Peter T "Dave Peterson" wrote in message ... I think xl2k added: range.pastespecial paste:=xlPasteColumnWidths And it even had a bug. MS didn't create that constant--except in the help. In xl2k, you would have to use: range.pastespecial paste:=8 Peter T wrote: "Mike Middleton" wrote in message As I recall, in Excel 97, I had to use three separate Copy and Paste commands: one for cell values, one for cell formats, and one for column widths. I'll stand corrected but I don't think Excel 97 has any facility to paste column widths unless an entire sheet is copied to a new sheet. There is of course PasteSpecial that can paste values or formats and some other things, but not row/column dim's. Regards, Peter T -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 97: Copy Range with all formatting to another Sheet
Try using:
Selection.PasteSpecial Paste:=8 Peter T wrote: Weird, in XL2k a recorded macro returns - Selection.PasteSpecial Paste:=xlColumnWidths ' etc But it fails on playback as the named constant is not found Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... Indeed it was introduced in XL2000 but it's not even documented in XL2000's help at all "Paste Optional Variant. The part of the range to be pasted. Can be one of the following XlPasteType constants: xlPasteAll, xlPasteFormulas, xlPasteValues, xlPasteFormats, xlPasteNotes, or xlPasteAllExceptBorders. The default value is xlPasteAll." This works in xl2k but fails in xl97 range.pastespecial paste:=xlPasteColumnWidths However, in XL97 if entire columns are copied then Pastespecial formats will paste column widths. That requires of course that the destination is one or same number of columns as the source. Regards, Peter T "Dave Peterson" wrote in message ... I think xl2k added: range.pastespecial paste:=xlPasteColumnWidths And it even had a bug. MS didn't create that constant--except in the help. In xl2k, you would have to use: range.pastespecial paste:=8 Peter T wrote: "Mike Middleton" wrote in message As I recall, in Excel 97, I had to use three separate Copy and Paste commands: one for cell values, one for cell formats, and one for column widths. I'll stand corrected but I don't think Excel 97 has any facility to paste column widths unless an entire sheet is copied to a new sheet. There is of course PasteSpecial that can paste values or formats and some other things, but not row/column dim's. Regards, Peter T -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 97: Copy Range with all formatting to another Sheet
Hi Dave,
Try using: Selection.PasteSpecial Paste:=8 Yes that works, it's just odd that somehow the macro recorder returns the named constant that's not recognized by xl2k's VBA. A couple of posts back this This works in xl2k but fails in xl97 range.pastespecial paste:=xlPasteColumnWidths should have read This works in xl2k but fails in xl97 range.pastespecial paste:=8 Regards, Peter T "Dave Peterson" wrote in message ... Try using: Selection.PasteSpecial Paste:=8 Peter T wrote: Weird, in XL2k a recorded macro returns - Selection.PasteSpecial Paste:=xlColumnWidths ' etc But it fails on playback as the named constant is not found Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... Indeed it was introduced in XL2000 but it's not even documented in XL2000's help at all "Paste Optional Variant. The part of the range to be pasted. Can be one of the following XlPasteType constants: xlPasteAll, xlPasteFormulas, xlPasteValues, xlPasteFormats, xlPasteNotes, or xlPasteAllExceptBorders. The default value is xlPasteAll." This works in xl2k but fails in xl97 range.pastespecial paste:=xlPasteColumnWidths However, in XL97 if entire columns are copied then Pastespecial formats will paste column widths. That requires of course that the destination is one or same number of columns as the source. Regards, Peter T "Dave Peterson" wrote in message ... I think xl2k added: range.pastespecial paste:=xlPasteColumnWidths And it even had a bug. MS didn't create that constant--except in the help. In xl2k, you would have to use: range.pastespecial paste:=8 Peter T wrote: "Mike Middleton" wrote in message As I recall, in Excel 97, I had to use three separate Copy and Paste commands: one for cell values, one for cell formats, and one for column widths. I'll stand corrected but I don't think Excel 97 has any facility to paste column widths unless an entire sheet is copied to a new sheet. There is of course PasteSpecial that can paste values or formats and some other things, but not row/column dim's. Regards, Peter T -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 97: Copy Range with all formatting to another Sheet
I think that even MS would acknowledge this as a bug in xl2k.
Peter T wrote: Hi Dave, Try using: Selection.PasteSpecial Paste:=8 Yes that works, it's just odd that somehow the macro recorder returns the named constant that's not recognized by xl2k's VBA. A couple of posts back this This works in xl2k but fails in xl97 range.pastespecial paste:=xlPasteColumnWidths should have read This works in xl2k but fails in xl97 range.pastespecial paste:=8 Regards, Peter T "Dave Peterson" wrote in message ... Try using: Selection.PasteSpecial Paste:=8 Peter T wrote: Weird, in XL2k a recorded macro returns - Selection.PasteSpecial Paste:=xlColumnWidths ' etc But it fails on playback as the named constant is not found Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... Indeed it was introduced in XL2000 but it's not even documented in XL2000's help at all "Paste Optional Variant. The part of the range to be pasted. Can be one of the following XlPasteType constants: xlPasteAll, xlPasteFormulas, xlPasteValues, xlPasteFormats, xlPasteNotes, or xlPasteAllExceptBorders. The default value is xlPasteAll." This works in xl2k but fails in xl97 range.pastespecial paste:=xlPasteColumnWidths However, in XL97 if entire columns are copied then Pastespecial formats will paste column widths. That requires of course that the destination is one or same number of columns as the source. Regards, Peter T "Dave Peterson" wrote in message ... I think xl2k added: range.pastespecial paste:=xlPasteColumnWidths And it even had a bug. MS didn't create that constant--except in the help. In xl2k, you would have to use: range.pastespecial paste:=8 Peter T wrote: "Mike Middleton" wrote in message As I recall, in Excel 97, I had to use three separate Copy and Paste commands: one for cell values, one for cell formats, and one for column widths. I'll stand corrected but I don't think Excel 97 has any facility to paste column widths unless an entire sheet is copied to a new sheet. There is of course PasteSpecial that can paste values or formats and some other things, but not row/column dim's. Regards, Peter T -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 97: Copy Range with all formatting to another Sheet
Peter T -
I'll stand corrected, too. I was likely using separate column autofits instead of column width pastes. - Mike "Peter T" <peter_t@discussions wrote in message ... "Mike Middleton" wrote in message As I recall, in Excel 97, I had to use three separate Copy and Paste commands: one for cell values, one for cell formats, and one for column widths. I'll stand corrected but I don't think Excel 97 has any facility to paste column widths unless an entire sheet is copied to a new sheet. There is of course PasteSpecial that can paste values or formats and some other things, but not row/column dim's. Regards, Peter T |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 SP3 Copy Worksheet with another sheet named range now # | Excel Discussion (Misc queries) | |||
Copy Named Range from one sheet to another with formatting | Excel Programming | |||
Copy Excel Sheet to another sheet and preserve formatting? | Excel Programming | |||
copy range from one excel sheet to other fails. | Excel Programming | |||
How do I edit a selected range then copy the range into an new sheet??? | Excel Programming |