Macro to copy worksheet into word

**cannon_lab** · 05-21-2008, 06:37 AM

Hi all,
I need a macro that will copy a worksheet into a new word document. I don't want the data to be linked, I just want it to paste the values, like when you do Paste Special> Values. There is a specify range of data I want to copy- Cells B2:H80.

I've created the following code from stuff I found on the internet and by adjusting it a bit. I'm not a programmer, so this is a shot in the dark.

Sub OpenSpecificDoc()
Dim wrdApp As Word.Application

Set wrdApp = CreateObject("word.Application")

wrdApp.Documents.Add

wrdApp.Visible = True

Range("b2:h88").Select
Selection.Copy

wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteMetafilePicture, _
Placement:=wdInLine, DisplayAsIcon:=False

End Sub

The problem with this is that it doesn't paste the entire selection, can anyone explain why it doesn't and how I can remedy this?
Thank you,
Pete

**Greg M** · 05-21-2008, 07:50 AM

Hi Pete,

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

Please Login or Register  to view this content.

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

**cannon_lab** · 05-21-2008, 12:01 PM

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

**Greg M** · 05-21-2008, 07:33 PM

Hi again Pete,

Here's a cheap & cheerful way of accommodating the table on a single sheet. The macro routine adjusts the top & bottom margins and the table row height so that all of the information fits on a single sheet.

Just use the following for the "With appWord / End With" chunk of my previous code:

Please Login or Register  to view this content.

I'll see if I can find out why your approach doesn't seem to be working correctly.

Regards,

Greg M

P.S. Oops! I've just realised you want to copy rows 2 to 80, not 20 to 80 as my code does - sorry!

**Greg M** · 05-21-2008, 08:01 PM

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

Macro to copy worksheet into word

LinkBack

Thread Tools

Rate This Thread

Display

Macro to copy worksheet into word

Ongoing Issues

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions