Paste range to fit one page in word.

[scho23]

I am a real VBA novice. When I highlight copy and paste my range into word it looks how i want it all on one page. The macro recorder in word says:
Selection.PasteExcelTable False, False, False

When I use the code in excel it spreads over two pages:

Dim appWD As Word.Application
Set appWD = CreateObject("Word.Application.12")
appWD.Documents.Add
appWD.Selection.PasteExcelTable False, False, False


How do i paste it into word so that it maintains its stucture and fits on one page just like doing it manually?

Please help, thank you

[GTO]

Greetings scho23,

I have ver 9 at home (2000), so same commands not available. Thus, you may wish to adjust below. Either appear to work, while I would suspect adding it as an OLE object/shape would keep size best.

Hope this helps,

Mark

Option Explicit
Dim objWORD As Object
Dim objDOC As Object

Sub Word_CopyTableTo()
Dim rngSelection As Range

'// Change to in-line error handling, to see if GetObject fails. //
On Error Resume Next
Set objWORD = GetObject(Class:="Word.Application")

If Err.Number > 0 Then
'// If there was an error getting the object, it didn't exist (that is, //
'// Word wasn't already running), so we need to create it. //
Set objWORD = CreateObject(Class:="Word.Application")
Err.Clear
End If

'//Reset error handling//
On Error GoTo 0

objWORD.Application.Visible = True

Set objDOC = objWORD.Documents.Add

Set rngSelection = Selection

rngSelection.Copy

objDOC.Application.Selection.Paste

' objDOC.Application.Selection.InlineShapes.AddOLEObject _
'ClassType:="Excel.Sheet.8", Filename:= _
'"C:\Documents and Settings\MARK\Desktop\New Folder\Word.xls", _
'LinkToFile:=False, DisplayAsIcon:=False

Set objWORD = Nothing
Set objDOC = Nothing
End Sub

[scho23]

Your code is probabily perfect, however the same problem happens whereby the paste is different using VBA than if i copy and paste.

If you run the macros in the wookbook then the selection ends up on two word pages. If you copy and paste manually it fits nicely onto one.

10497

All help is really gratefully recieved.

[GTO]

Well shucks sho, sorry about that.

I do not have 2007; but am sure someone can take a peek.

Mark