 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / May 2008Excel 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 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 > Hello all, > [quoted text clipped - 5 lines] > Thanks and greetings > Udo 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 > Hello all, > [quoted text clipped - 5 lines] > Thanks and greetings > Udo "Mike Middleton" <mike@mikemiddleton.com> 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 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 > "Mike Middleton" <mike@mikemiddleton.com> wrote in message > [quoted text clipped - 9 lines] > Regards, > Peter T  SignatureDave Peterson 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 >I think xl2k added: > range.pastespecial paste:=xlPasteColumnWidths [quoted text clipped - 20 lines] >> Regards, >> Peter T 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 > Indeed it was introduced in XL2000 but it's not even documented in > XL2000's help at all [quoted text clipped - 40 lines] >>> Regards, >>> Peter T Try using: Selection.PasteSpecial Paste:=8 > Weird, in XL2k a recorded macro returns - > [quoted text clipped - 53 lines] > >> > >> Dave Peterson SignatureDave Peterson 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 > Try using: > Selection.PasteSpecial Paste:=8 [quoted text clipped - 56 lines] > > >> > > >> Dave Peterson I think that even MS would acknowledge this as a bug in xl2k. > Hi Dave, > [quoted text clipped - 87 lines] > > > > Dave Peterson SignatureDave Peterson Peter T - I'll stand corrected, too. I was likely using separate column autofits instead of column width pastes. - Mike > "Mike Middleton" <mike@mikemiddleton.com> wrote in message > [quoted text clipped - 10 lines] > Regards, > Peter T |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.