how to copy different text from word into excel on consecutive rows

mihnea96 · #1 05-08-2017, 07:05 AM

Hi! Could you kindly help me with my problem: I want to copy some text from Word into Excel, using a command button. The idea is that i select some text, press the button and it pastes the selected text in b2, for example, and when I select another text, I cannot make it to paste the secondly selected text in b3, and so on.

This is the code i'm trying to use. Could you explain me, please, what's wrong about it and help me fix it?

Thank you!

Code:

Private Sub CommandButton2_Click()
Selection.Copy
Dim objXL As Object
Set objXL = GetObject(, "excel.application")

Dim NumRows As Long
Dim xlsRow As Row
xlsRow = xlsRow + 1
objXL.Application.Cells(xlsRow, 2) = Selection.Text
Selection.Paste

End Sub

gmayor · #2 05-08-2017, 07:58 AM

Assuming the workbook is open at the sheet you want to process then

Code:

Private Sub CommandButton2_Click()
Dim objXL As Object
Dim NumRows As Long
Dim xlsNextRow As Long
Dim xlSheet As Object
    If Len(Selection) > 1 Then
        Set objXL = GetObject(, "excel.application")
        Set xlSheet = objXL.activesheet
        xlsNextRow = xlSheet.Range("B" & xlSheet.Rows.Count).End(-4162).Row + 1
        xlSheet.Cells(xlsNextRow, 2) = Selection.Text
    Else
        MsgBox "Nothing selected"
    End If
    Set objXL = Nothing
    Set xlSheet = Nothing
End Sub

mihnea96 · #3 05-08-2017, 08:20 AM

It works perfectly! Thank you very much!
Would it be too much if I'd ask how to make it bold?

gmayor · #4 05-08-2017, 10:06 AM

Add

Code:

xlSheet.Cells(xlsNextRow, 2).Font.Bold = True

immediately before 'Else' i.e.

Code:

    xlSheet.Cells(xlsNextRow, 2) = Selection.Text
        xlSheet.Cells(xlsNextRow, 2).Font.Bold = True
    Else

mihnea96 · #5 05-08-2017, 12:09 PM

It works great! Thank you very much!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel VBA macro to copy specific rows and delete it	pourmalla	Excel Programming	2	04-02-2017 12:09 AM
Counting Consecutive Periods In Rows Using Numbers	Yaaseen23	Excel	18	03-24-2017 11:15 AM
Copy Underline text from Word and Paste into excel	rfaris	Excel Programming	7	10-05-2015 05:56 AM
Is it possible to copy non-contiguous rows of a Table and paste them as a separate Table in Word?	Joey Cheung	Word Tables	1	08-12-2014 05:15 PM
Find Results in excel copy the rows to another sheet	khalidfazeli	Excel	2	02-06-2013 09:38 AM