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