How to insert paragraphs between text and then export to word

[attikuz]

Hello!

I have some code that combines text together. Because of the number of characters I have to export the text to word. I have two issues:

1) The formatting between the text is not correct. I want to apply spacing between my variables and then a page break between each new row.

2) I am really struggling to export to word and autofit the contents.

Here is my code:

Sub inbrief()

Dim Cash As Range
Dim Title, activity, Description As String
Dim cell As Range
Dim CurrentRow, LastRow As Integer

Application.ScreenUpdating = False

CurrentRow = 3
 
'Clear sheet and filters
Sheets("In Briefs").Activate
Sheets("In Briefs").Range("$A$2:$H$1000").AutoFilter Field:=3
Sheets("In Briefs").Range("A3:H1000").ClearContents

'Copy SEDOLs from portfolio tab and bring in in briefs
Sheets("Portfolio").Range("A8:A100").Copy
Sheets("In Briefs").Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues

'Remove all the blank rows
LastRow = ActiveSheet.UsedRange.Rows.Count
Range("A3:A" & LastRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

'Copy the vlookup formulas to import the paras
Range("B1:D1").Copy
Range("B3:D" & LastRow).PasteSpecial xlPasteFormulas

'Join the header, activity and inbrief together and apply formatting
For Each cell In Range("F3:F" & LastRow)

If Range("C" & CurrentRow) <> "" Then
    With Application
        Title = Range("B" & CurrentRow).value
        Description = Range("C" & CurrentRow).value & Chr(13)
        activity = Range("D" & CurrentRow).value
    End With
        
        cell.value = Title & activity & Description
        
    With cell
        .Characters(1, Len(Title)).Font.Color = RGB(0, 89, 85)
        .Characters(1, Len(Title)).Font.Size = 18
        .Characters(Len(Title) + 1, Len(activity)).Font.Size = 11
        .Characters(Len(Title) + 1, Len(activity)).Font.Color = vbBlack
        .Characters(Len(Title) + Len(activity) + 1, Len(Description)).Font.Size = 11
        .Characters(Len(Title) + Len(activity) + 1, Len(Description)).Font.Color = vbBlack
     End With
End If

CurrentRow = CurrentRow + 1
Next

'Filter out any securities that have no data
With Range("$A$3:$H$" & LastRow)
        .AutoFilter Field:=3, Criteria1:="="
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With

Application.ScreenUpdating = True

End Sub

I've attached a screenshot of what it looks like.

Please can someone help me with the formatting and with exporting to word?

Thanks!

 

[Micron]

Not sure I can help but maybe this thread will provide some direction.

I want to copy multiple cells as one sentence to paste in another program

Hey, I'm looking to create a table with pre-determined sentences in, so that all I need to do is add a person's name in the middle cell, and hit copy on a command button, and I will have copied the multiple cells, to be able to copy as one sentence in another program elsewhere. I have found a...

www.mrexcel.com

To add a new paragraph, use vbCrLf or Chr(13) & Chr(10). I could only see that you added Chr(13), which is not correct.
To add space between variables try concatenation: variableName1 & " " & variableName2
In a multi line declaration, you must explicitly type each variable as in
Dim Title As String, activity As String, Description As String

In your line, only Description is a string, the rest are variants because you didn't specify the data type. As long as the code can correctly interpret what you pass to the variable it will probably work. However, coding as if you expect a variable to hold a string when it can hold any data type is not a good idea.

Description might be a vba reserved word - it's a reserved word in Access but I doubt it will cause any issues for you. After this, I'm going to look for a list of reserved words for Excel.

As for your issue #2, I don't know enough about it to be of any help.

EDIT- apparently it is reserved. About half way down the page here is an Excel list

VBA reserved words |

www.audeser.com