Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: Copy/Paste with format/style
Hi again,
I posted the message below yesterday, and received a reply from Mike H; unfortunately, that response was only useful for a minor tweak to the code, and my main issue remains. I think that, in a nutshell, I need a definitive answer to whether a copy/paste operation via code, between sheets in different workbooks, will preserve the formatting from the sheet being copied from? I'm not sure that it's possible; when I do manual cut/paste between sheets in different workbooks, the formatting is not preserved - even with PasteSpecial All. I'm reposting because I suspect that my original message will not attract further attention, since there is a thread of replies. Thanks again, Rob ----- Original Message ----- I'm trying to copy/paste a range from a sheet (the only sheet) in one workbook to a sheet in a different workbook. Almost everything works, except that I lose all the formatting applied to different cells within the range. The section of code I've got looks like this: Workbooks.Open Filename:=strFile Windows(strFile).Activate 'Select section of WBS report containing required data 'and copy to this sheet, starting at the next empty cell in Column B Range("B5:I5").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Windows(strWorkbookName).Activate Range("B1").Select 'move to next blank cell Selection.End(xlDown).Select ActiveCell.Offset(1).Select ActiveSheet.Paste Application.CutCopyMode = False I've tried using ActiveSheet.PasteSpecial xlAll, but that doesn't help. I suspect that what I need to do to get the formatting across is to actually copy the worksheet I'm copying from into the other workbook, copy between the sheets, then delete the copied worksheet. Is this the way to go, or have I missed something blindingly obvious? Note: I'm actually an Access developer, so I'm not overly familiar with the Excel object model, and the various properties and methods available. Any hints on cleaner coding for what I'm doing would also be appreciated. TIA, Rob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: Copy/Paste with format/style
On Apr 9, 8:14 pm, "Rob Parker"
wrote: Hi again, I posted the message below yesterday, and received a reply from Mike H; unfortunately, that response was only useful for a minor tweak to the code, and my main issue remains. I think that, in a nutshell, I need a definitive answer to whether a copy/paste operation via code, between sheets in different workbooks, will preserve the formatting from the sheet being copied from? I'm not sure that it's possible; when I do manual cut/paste between sheets in different workbooks, the formatting is not preserved - even with PasteSpecial All. I'm reposting because I suspect that my original message will not attract further attention, since there is a thread of replies. Thanks again, Rob ----- Original Message ----- I'm trying to copy/paste a range from a sheet (the only sheet) in one workbook to a sheet in a different workbook. Almost everything works, except that I lose all the formatting applied to different cells within the range. The section of code I've got looks like this: Workbooks.Open Filename:=strFile Windows(strFile).Activate 'Select section of WBS report containing required data 'and copy to this sheet, starting at the next empty cell in Column B Range("B5:I5").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Windows(strWorkbookName).Activate Range("B1").Select 'move to next blank cell Selection.End(xlDown).Select ActiveCell.Offset(1).Select ActiveSheet.Paste Application.CutCopyMode = False I've tried using ActiveSheet.PasteSpecial xlAll, but that doesn't help. I suspect that what I need to do to get the formatting across is to actually copy the worksheet I'm copying from into the other workbook, copy between the sheets, then delete the copied worksheet. Is this the way to go, or have I missed something blindingly obvious? Note: I'm actually an Access developer, so I'm not overly familiar with the Excel object model, and the various properties and methods available. Any hints on cleaner coding for what I'm doing would also be appreciated. TIA, Rob A manual cut and paste (as well as a manual copy and paste) from one file to a different file preserves the format for me .... am I missing something? ps. if I use your code, which works fine, the formatting is also copied over ... Chris |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: Copy/Paste with format/style
Thanks Chris,
It certainly doesn't work for me - using Excel 2002 at home, or Excel 2003 at work. That's why I posted the question ;-) What version of Excel are you using? Or have I perchance got something set wrong - or at least differently to you - in my Options (not that there's anything that grabs my attention as being applicable to this)? Rob "cht13er" wrote in message ... <snip A manual cut and paste (as well as a manual copy and paste) from one file to a different file preserves the format for me .... am I missing something? ps. if I use your code, which works fine, the formatting is also copied over ... Chris |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: Copy/Paste with format/style
Hi Rob, for what it is worth, I formatted a cell in one workbook with font,
borders and interior color. Used your code, changing only the workbook name, of course, and it copied everything over. I am using XP xl2003. I also tried changing some of the option settings in the edit tab and still got everything copied over. You should not be getting those results on your home and office sets unless you have something somewhere else in the code you are running that turns something off. You need to check the code for all commands that have an ending of Enabled = False to see if that would affect the copy and paste attributes. "Rob Parker" wrote: Thanks Chris, It certainly doesn't work for me - using Excel 2002 at home, or Excel 2003 at work. That's why I posted the question ;-) What version of Excel are you using? Or have I perchance got something set wrong - or at least differently to you - in my Options (not that there's anything that grabs my attention as being applicable to this)? Rob "cht13er" wrote in message ... <snip A manual cut and paste (as well as a manual copy and paste) from one file to a different file preserves the format for me .... am I missing something? ps. if I use your code, which works fine, the formatting is also copied over ... Chris |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: Copy/Paste with format/style
Thanks for the comments.
Seems like I'm the only one with this problem. I've had another careful look at all the options, and still can't see anything that looks even vaguely relevant; and there's nothing in my code that has anything ending with .Enabled = False. The only other stuff in my code is to set the various string variables before I start copying. And, at the moment, I've got some additional code after the copy operations are complete to tidy up the new sheet. C'est la vie ... Rob "JLGWhiz" wrote in message ... Hi Rob, for what it is worth, I formatted a cell in one workbook with font, borders and interior color. Used your code, changing only the workbook name, of course, and it copied everything over. I am using XP xl2003. I also tried changing some of the option settings in the edit tab and still got everything copied over. You should not be getting those results on your home and office sets unless you have something somewhere else in the code you are running that turns something off. You need to check the code for all commands that have an ending of Enabled = False to see if that would affect the copy and paste attributes. "Rob Parker" wrote: Thanks Chris, It certainly doesn't work for me - using Excel 2002 at home, or Excel 2003 at work. That's why I posted the question ;-) What version of Excel are you using? Or have I perchance got something set wrong - or at least differently to you - in my Options (not that there's anything that grabs my attention as being applicable to this)? Rob "cht13er" wrote in message ... <snip A manual cut and paste (as well as a manual copy and paste) from one file to a different file preserves the format for me .... am I missing something? ps. if I use your code, which works fine, the formatting is also copied over ... Chris |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: Copy/Paste with format/style
It seems that my problem is that described in KB917637. I do notice that
when my code runs, a separate instance of Excel appears in my taskbar. The Workbooks.Open method doesn't have any parameter to specify whether the newly opened workbook should open a new instance of Excel, and (again) I can't see any obvious option to set this. Is there a registry setting involved here? The KB article simply says that the work-around is to open both workbooks in a single instance of Excel, but it doesn't say how to do this (and further searching of the KB has proved fruitless). Any ideas? TIA again, Rob "Rob Parker" wrote in message ... Thanks for the comments. Seems like I'm the only one with this problem. I've had another careful look at all the options, and still can't see anything that looks even vaguely relevant; and there's nothing in my code that has anything ending with .Enabled = False. The only other stuff in my code is to set the various string variables before I start copying. And, at the moment, I've got some additional code after the copy operations are complete to tidy up the new sheet. C'est la vie ... Rob "JLGWhiz" wrote in message ... Hi Rob, for what it is worth, I formatted a cell in one workbook with font, borders and interior color. Used your code, changing only the workbook name, of course, and it copied everything over. I am using XP xl2003. I also tried changing some of the option settings in the edit tab and still got everything copied over. You should not be getting those results on your home and office sets unless you have something somewhere else in the code you are running that turns something off. You need to check the code for all commands that have an ending of Enabled = False to see if that would affect the copy and paste attributes. "Rob Parker" wrote: Thanks Chris, It certainly doesn't work for me - using Excel 2002 at home, or Excel 2003 at work. That's why I posted the question ;-) What version of Excel are you using? Or have I perchance got something set wrong - or at least differently to you - in my Options (not that there's anything that grabs my attention as being applicable to this)? Rob "cht13er" wrote in message ... <snip A manual cut and paste (as well as a manual copy and paste) from one file to a different file preserves the format for me .... am I missing something? ps. if I use your code, which works fine, the formatting is also copied over ... Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lock Cell Format - Allow copy and paste of data without format change | Excel Worksheet Functions | |||
Copy/Paste with format/style | Excel Programming | |||
Changing from format style to list style | Excel Worksheet Functions | |||
Repost - Is there a quicker way to copy paste | Excel Programming | |||
Copy & paste image from UserForm (repost) | Excel Programming |