Repost: Copy/Paste with format/style

[Rob Parker]

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

 

[cht13er]

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

 

[Rob Parker]

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

 

[JLGWhiz]

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]

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

 

[Rob Parker]

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

 

[JLGWhiz]

If you do solve the problem, I hope you will post back and share the remedy
with us.

 

[cht13er]

If you do solve the problem, I hope you will post back and share the remedy
with us.







- Show quoted text -

The main thing I understood from the Microsoft post re: 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

 

[Rob Parker]

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