Excel VBA : Range to Word / Worksheet to Word

Ekounerio

New Member
Joined
Dec 11, 2017
Messages
7
Hello,

I need a bit of help in something I'm having a bit more problems.
I'm trying to find/make a code that would able me to select a certain range and copy/past it into a new Word document with a Macro.
Exactly the same idea too for a whole Worksheet (Don't know if it's possible to do it page by page, in the same format as you would print it.

I've founds already some codes online but I somehow don't manage to make them fit (or I miss the understanding of the code for it to do what I expect.)

Has anyone already experience with this type of Macro ?

Best Regards
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Nine Zero

Well-known Member
Joined
Mar 10, 2016
Messages
622
Hello,

First make sure you allow the Word reference in the vba editor do this by going to
Tools >> References and look for "Microsoft Word 16.0" (your version might be different but it will be the microsoft word one

paste this code into a vba module

Code:
Sub PasteToWord()
     
    Dim AppWord         As Word.Application
     
    Set AppWord = CreateObject("Word.Application")
    AppWord.Visible = True
    Selection.Copy
    AppWord.Documents.Add
    AppWord.Selection.Paste
     
    Application.CutCopyMode = False
     
    Set AppWord = Nothing
     
End Sub

Make sure you select your data first before running the macro
 

Ekounerio

New Member
Joined
Dec 11, 2017
Messages
7
Seems to work quite nicely !
Perfect !

Now for the second question I've been trying to extract a worksheet with only it's values and without the code on it to a new WorkBook.
Code:
Sub Worksheet()
Sheets("Order form TNW v2").Copy
With ActiveSheet.UsedRange
    .Value = .Value
End With
End Sub

The code I have to copy the sheet is this one, though when I try to add a part to delete de line codes
Code:
With ActiveSheet.UsedRange.CodeModule
    .DeleteLines 1, .CountOfLines
End With

It seems to have problems, as it opens a new book, but the cells copied are not Values anymore.
 

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,511
Now for the second question I've been trying to extract a worksheet with only it's values and without the code on it to a new WorkBook.
So why not just save the new workbook in the xlsx format? Trying to delete code via code requires giving access to the VBA project object model, which can't be done via code, and is a significant security risk.
 

Ekounerio

New Member
Joined
Dec 11, 2017
Messages
7
So why not just save the new workbook in the xlsx format? Trying to delete code via code requires giving access to the VBA project object model, which can't be done via code, and is a significant security risk.

That's overall a good idea. I think I will simply do that, and if the person wishes to work as xlsm, the change can always be done. It was mostly to avoid the error messages to pop up
 

Watch MrExcel Video

Forum statistics

Threads
1,122,326
Messages
5,595,519
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top