Excel VBA : Range to Word / Worksheet to Word

[Ekounerio]

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

 

[Nine Zero]

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

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]

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.

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

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]

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]

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