What is wrong with this code?

[TT]

Hello,

I´m trying to paste two cells (text) from Excel to Word. The code pasted
below does not completely do the trick - please could someone tell me what
is the problem, what must be added?

Problems:
1. If I leave out the ".Collapse wdCollapseEnd", the range A11 gets
replaced by the range A2:A6. Then the range A2:A6 is formatted like I want
it to be (bold).
2. If I dont leave it out, the formatting is not correct, but both ranges
are being copied otherwise correctly. Then the range A2:A6 is not being
formatted at all, but copies the formatting from range A11 (not bold).



Sub TW()

Dim AppWD As Word.Application
Dim DocWD As Word.Document
Dim RangeWD As Word.Range

Set AppWD = CreateObject("Word.Application.10")
AppWD.Visible = True

Set DocWD = AppWD.Documents.Add
With DocWD
Set RangeWD = .Range
Sheets("T").Select
Range("A11").Select
Selection.Copy
With RangeWD
..Font.Name = "Arial"
..Font.Size = 12
..Font.Bold = False
..PasteAndFormat Type:=wdFormatPlainText
..Collapse wdCollapseEnd
Sheets("T").Select
Range("A2:A6").Select
Selection.Copy
With RangeWD
..Font.Name = "Arial"
..Font.Size = 12
..Font.Bold = True
..PasteAndFormat Type:=wdFormatPlainText
..Collapse wdCollapseEnd
End With
End With

End With

End Sub

 

[Helmut Weber]

Hi,
be precise, because

I´m trying to paste two cells (text) from Excel to Word.

you are trying to paste 2 Excel-Ranges, not cells.

Furthermore, if you collapse the range,
it stays collapsed, and you apply your formatting
to a range of length 0.

How about this one, with improvements possible, I think:
Sub TW()

Dim AppWD As Word.Application
Dim DocWD As Word.Document
Dim RangeWD As Word.Range

Set AppWD = CreateObject("Word.Application.10")
AppWD.Visible = True

Set DocWD = AppWD.Documents.Add
With DocWD
Set RangeWD = .Range
Sheets("T").Select
Range("A11").Select
Selection.Copy
With RangeWD
.Font.Name = "Arial"
.Font.Size = 12
.Font.Bold = False
.PasteAndFormat Type:=wdFormatPlainText
.InsertAfter vbCr
.Start = .End + 1
.Collapse wdCollapseEnd
MsgBox Len(RangeWD.Text) ' for testing
Sheets("T").Select
Range("A2:A6").Select
Selection.Copy
.End = DocWD.Content.End ' extent the range
.Font.Bold = True
.PasteAndFormat Type:=wdFormatPlainText
End With
End With

End Sub

Greetings from Bavaria, Germany

Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/

 

[TT]

Thanks a lot Helmut! Seems to work fine.

This is all new stuff to me. Thanks for helping me out here.

TT