CODEWITHSUNDEEP
excelvbams-word
Mazze
Mazze

Reputation: 453

Copy text from excel cell to word if condition is met

I'm designing an excel worksheet, where I can add the text from certain cells to a new word document if a condition for the each cell is met.

My code pastes the text from the cell to the new word document. But it always replaces the text from the previous cell. So only the last cell is visible. How can I change that?

Private Sub CommandButton1_Click()

Dim WrdApp As Word.Application
Dim WrdDoc As Word.Document

Set WrdApp = New Word.Application
    WrdApp.Visible = True
    WrdApp.Activate

Set WrdDoc = WrdApp.Documents.Add
a = Worksheets("Tabelle1").Cells(Rows.Count, 1).End(xlUp).Row

For i = 6 To a

    If Worksheets("Tabelle1").Cells(i, 5).Value = "Ja" Then
        
        Worksheets("Tabelle1").Cells(i, 4).Copy
        WrdDoc.Paragraphs(1).Range.PasteSpecial xlPasteValues
        
    End If
Next

Application.CutCopyMode = False

End Sub

Upvotes: 0

Views: 1082

Answers (1)

Timothy Rylatt
Timothy Rylatt

Reputation: 7860

Your problem is that you are potentially pasting 6 times into the exact same location, leading to the text at that location being replaced each time. You need to think about what you would do if you were doing this task without code, and then write code that does the same.

You could start by changing:

WrdDoc.Paragraphs(1).Range.PasteSpecial xlPasteValues

to

WrdDoc.Characters.Last.PasteSpecial xlPasteValues

But you will still need to add something between each value you paste.

Upvotes: 1

Related Questions