Macro to copy worksheet into word

Hi Pete,

Copy the following into a standard VBA code module in the workbook from which you want to copy data.

Option Explicit


Const strRangeToCopy As String = "B20:H80"


Sub CopyRangeToWord()

    Dim appWord As Object

    Range(strRangeToCopy).Copy

    On Error Resume Next
        Set appWord = GetObject(, "Word.Application")
    On Error GoTo 0
    If appWord Is Nothing Then Set appWord = CreateObject("Word.Application")

    With appWord
        .Documents.Add
        .Selection.Paste
        .Visible = True
    End With

End Sub

You can alter the value of the constant strRangeToCopy to suit the range you wish to copy.

This routine will use an existing instance of Word if one exists, or will open a new one if Word is not already running.

Hope the above helps. Please let me know how you get on.

Reards,

Greg M

Thanks Greg, that works well, but I was hoping for more of a fix on what I had out together.

The code I wrote made is more like a picture being pasted onto the word document, which is what I required. The code that you have written is more like a copy and paste, which means that the range selected will go over two pages. Also, I can set the page set up in Excel for it to fit on one page, but using your code the far right hand side is cut off- it falls off the end and needs resizing.

Essentially I need to why the code I wrote didn't copy the entire range.

Alternatively, how can edit the code you have written to make the range fit onto one page?

Any help?

Thanks,
Pete

Hi Pete,

At the risk of stating the obvious, I think the amount of information you're trying to paste as a metafile is just too much to fit on a single sheet. Almost certainly all of the data are being copied to the clipboard, but when the paste operation is performed the application fits as much of the picture as it can onto the sheet & just forgets about the remainder.

I think you're going to have to try the "less sophisticated" approach like playing with margins, row heights & font sizes if you want to fit everything on a single sheet.

Best regards,

Greg M