[RESOLVED]Copying an Excel Column to a Word Doc

[pickarooney]

I have a series of Excel spreadsheets, each with multiple worksheets.
I'd like to copy a certain column (user-defined) from each worksheet on the spreadsheet and paste it to a word doc, with the name of the worksheet above each copied column (the columns become tables when pasted to Word).

The contents of the clipboard seem to get lost when moving between the two applications. What would be the best way to transfer the data from one program to the other, retaining the cell-style layout?

What I have so far is:

Sub CopytoDocument()
 
    Dim cColumn As String
    Dim WordObj As Object
    Dim CellContents As String
    On Error Resume Next
    Err.Number = 0
    
    Set WordObj = GetObject(, "Word.Application")
    If Err.Number = 429 Then
        Set WordObj = CreateObject("Word.Application")
        Err.Number = 0
    End If
    
    cColumn = InputBox("Which column (indicate the letter)?")
    Columns(cColumn & ":" & cColumn).Select
    
    Selection.Copy
    'CellContents = Selection
    
    WordObj.Visible = True
    WordObj.Documents.Add
    
    Selection.Paste
    
End Sub

[RobDog888]

Since both Excel and Word have a "Selection" object you need to differenciate between the two. Try creating variables
like xlSel and wdSel. You may do beter with the clipboard.copy and clipboard.paste.

[pickarooney]

I tried Clipboard.Copy but although it compiles, it doesn't seem to do anything, and the help file has no mention of this object.

In order to use the xlSel and wdSel variables, what type should I declare them as? There seems to be no Selection object. My VB editor in Excel is acting strangely, it doesn't offer me any choices of objects/properties when I type the variable name followed by a dot, the way it does in Word.

[pickarooney]

Ahh, I get it now! Here's the (qorking) preliminary code - cheers!

Sub CopytoDocument()
 
    Dim cColumn As String
    Dim WordObj As Object
    Dim ExcelObj As Object
    Dim CellContents As String
    On Error Resume Next
    Err.Number = 0
    
    Set WordObj = GetObject(, "Word.Application")
    If Err.Number = 429 Then
        Set WordObj = CreateObject("Word.Application")
        Err.Number = 0
    End If
    
    cColumn = InputBox("Which column (indicate the letter)?")
    Columns(cColumn & ":" & cColumn).Select
    
    Selection.Copy
    ExcelObj.PutToClipboard
        
    WordObj.Visible = True
    WordObj.Documents.Add
    
    WordObj.GetFromClipboard
    WordObj.Selection.Paste
    
End Sub