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