Hi,
I'm a VBA newbie - please tell me how to improve my post.
While trying to iterate over a series of columns and join them one at a time to another range using Union I find that the number of columns joined starts at one column, then two, then three and so on. How do I prevent this to just join on the first, then just the second, then just the third etc.?
My code is:
Code
Sub copyToWord()
Dim output As Range
Dim additionalCols As Range
Dim wrdApp As Word.Application
Set additionalCols = Range("J1", Range("J1").End(xlToRight).End(xlDown))
Set wrdApp = CreateObject("Word.Application")
Application.ScreenUpdating = False
With wrdApp
.Documents.Open "C:\Users\me\Doc1.docx"
.Selection.GoTo What:=wdGoToBookmark, Name:="here" 'Using a bookmark in the document
For Each col In additionalCols.Columns
Application.CutCopyMode = True
Set output = Union(Range("A1:I21"), col)
output.Copy
.Selection.PasteAndFormat (wdPasteDefault)
.Visible = True
With wrdApp.Selection
.Collapse Direction:=0
.InsertBreak Type:=7
End With
Next col
End With
.Documents.Save
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Data Copied Across", vbInformation, "Sample"
End Sub