Copy/Paste with format/style

[Rob Parker]

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

 

[Mike H]

Rob,

Tr this

Sub stantial()
strfile = "j:\book2.xls"
strWorkbookName = "Book1.xls"
Workbooks.Open Filename:=strfile
ActiveSheet.Range("B5:I5").Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWorkbook.Close savechanges:=False
Windows(strWorkbookName).Activate
Range("B1").Select
Cells(Rows.Count, 2).End(xlUp)(2).Select
ActiveCell.Offset(, 1).Select
Selection.PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False

End Sub

 

[Rob Parker]

Hi Mike,

Thanks for the reply.

However, it seems that all this does is a smarter way of finding the first
empty row to paste into. It doesn't paste with the formatting from the
sheet in strFile. As I said in my original post, using the .PasteSpecial
method, with the xlAll parameter, had already failed for me.

A further problem with your code is that it crashes, (I think) because the
sheet that the copy is from is closed before the paste operation - I had the
same problem in an earlier version of my code.

Can you tell me, with certainty, that a copy/paste operation via code,
between sheets in different workbooks, will preserve the formatting from the
sheet being copied from? That was the main point I was trying to resolve -
and, as I implied in my original post, I'm not sure that it's possible; when
I do manual cut/paste between sheets in different workbooks, the formatting
is not preserved.

Rob