VBA excel to word

The macro to create individual columns from the an Excel to Word document is:

Sub ExcelToWord()

‘ ExcelToWord Macro

‘Dim objWord As New Word.Application
Dim rng As Range, cell As Range
Set rng = Range(“A1:BB85”) ‘Change values to all columns and rows – this should be changed to count the active columns but it works for now
Set objWord = CreateObject(“Word.Application”)
objWord.Visible = True
objWord.Documents.Open “C:\Users\…\test.docx” ‘Change values as location to save it

For Each cell In rng
‘Copy the range Which you want to paste in a New Word Document
Range(“A1:B85”).Copy ‘Change values

With objWord.ActiveDocument
‘.Documents.Add
objWord.Selection.Paste
objWord.Visible = True
End With

Range(“B:B”).EntireColumn.Delete
Next cell

End Sub

Note: This macro will be copy and pasting from Excel to Word the column A + B where A are the headings and B the answers. It then deletes the B column and C becomes B and so on… be careful to change the number of columns to include or you can have an infinite loop.

Advertisements