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
"Mike H" <(E-Mail Removed)> wrote in message
news:ED16A709-BF76-4444-8F09-(E-Mail Removed)...
> 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" wrote:
>
>> 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
>>
>>
|
Similar Threads
