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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: Copy/Paste with format/style
If you do solve the problem, I hope you will post back and share the remedy
with us. "Rob Parker" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: Copy/Paste with format/style
On Apr 10, 11:28*am, JLGWhiz
wrote: If you do solve the problem, I hope you will post back and share the remedy with us. "Rob Parker" wrote: 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- Hide quoted text - - Show quoted text - The main thing I understood from the Microsoft post KB917637 was this: Excel needs to be opened only once for formats to paste. However, even if I open Excel twice and copy, I can paste the formats ... in other words, I'm unable to replicate the problem (Excel 2003 SP3). To check that you have your two workbooks open in the same instance of Excel, go "Window" and if both files open are listed, you should be OK (at least by KB917637). HTH, Chris |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: Copy/Paste with format/style
Thanks Chris,
Well, I now think the KB917637 isn't applicable. Both my workbooks appear in the window of either Excel entry on my taskbar, so it seems that they are running in a single instance of Excel. If I uncheck the View | Show | Windows in Taskbar option then I do only have one instance of Excel open. In fact, doing that revealed another potential problem: I was using: strWorkbookName = ThisWorkbook.Name to get the name of the workbook to copy into, but I found that this gives an error when I use it in Windows(strWorkbookName).Activate if there is more than one window open for the workbook. In that case: strWorkbookName = Windows(1).Caption 'active window is windows(1) is safer - since my macro is being run from a button on the workbook. But the copy operation is still refusing to include the format ;-( And, looking closer at what I'm getting, I think that it's copying some formatting, such as borders and cell pattern and merge cells, but not others such as font and fontstyle and wrap text and column widths. Very odd ... More if/when I find it. Meanwhile, if anyone else is following this thread and has any other suggestions, feel free to post them. I'm getting nowhere fast with this. Rob "cht13er" wrote in message ... <snip The main thing I understood from the Microsoft post KB917637 was this: Excel needs to be opened only once for formats to paste. However, even if I open Excel twice and copy, I can paste the formats ... in other words, I'm unable to replicate the problem (Excel 2003 SP3). To check that you have your two workbooks open in the same instance of Excel, go "Window" and if both files open are listed, you should be OK (at least by KB917637). HTH, 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 |