Macro that creates a word document (and then a PDF) very slow

[attikuz]

Hello MrExcel,

I have a macro that essentially exports text in 3 columns into a word document before converting to a PDF. I have had to do this as the text in 1 of the columns is well over 300 characters and can't be done in excel.

Unfortunately when i run the macro it takes about 13 minutes to complete. There are about 40 rows worth of data. Here is my macro:

Sub inbrief()

Dim Cash As Range
Dim Title, activity, Description As String
Dim cell As Range
Dim CurrentRow, LastRow As Integer
Dim wsBrief As Worksheet
Dim wb As Workbook
Dim LR As Long

Application.ScreenUpdating = False

CurrentRow = 2

Set wb = ActiveWorkbook
Set wsBrief = wb.Worksheets("In Briefs")
LR = Cells(Rows.Count, 1).End(xlUp).Row
 
'Clear sheet and filters
wsBrief.Activate
wsBrief.Range("$A$2:$D$1000").AutoFilter Field:=3
wsBrief.Range("A3:D" & LR).ClearContents

'Copy SEDOLs from portfolio tab and bring in in briefs
Sheets("Portfolio").Range("A8:A200").Copy
wsBrief.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

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

With wsBrief.Range("$B$3:$B$" & LastRow)
        .Font.Color = RGB(0, 89, 85)
        .Font.Size = 18
        .Font.Name = "Georgia"
        .WrapText = True
        .RowHeight = 14.25
End With

With wsBrief.Range("$C$3:$D$" & LastRow)
        .Font.Color = vbBlack
        .Font.Size = 11
        .Font.Name = "Georgia"
        .WrapText = True
        .RowHeight = 14.25
End With

Application.ScreenUpdating = True

End Sub

Sub ExcelToWord()

Call inbrief

'This macro exports the data on the "In Briefs" tab into a word document, creates a PDF called InBriefs.PDF, saves it to the user's H:/Brit folder and then closes the word document.
Application.ScreenUpdating = False
Application.StatusBar = "Launching Word ..."

Sheets("In Briefs").Activate

Dim WdApp As New Word.Application, WdDoc As Word.Document

With WdApp
Set WdDoc = .Documents.Add

Application.StatusBar = "Copying data ..."

    With WdDoc
    Sheets("In Briefs").Range("D2").Select
    
    Do Until IsEmpty(ActiveCell.Offset(1, -2).Range("A1"))
    ActiveCell.Offset(1, -2).Range("A1").Select
        Selection.Copy
    Application.Wait Now + (TimeValue("00:00:01"))
    .Range.Characters.Last.Paste
        ActiveCell.Offset(0, 1).Range("A1").Select
        Selection.Copy
    Application.Wait Now + (TimeValue("00:00:01"))
    .Range.Characters.Last.Paste
        ActiveCell.Offset(0, 1).Range("A1").Select
        Selection.Copy
    Application.Wait Now + (TimeValue("00:00:01"))
    .Range.Characters.Last.Paste
    Application.CutCopyMode = False
    Loop
    
    Application.StatusBar = "Finishing up ..."
    .ExportAsFixedFormat OutputFilename:="H:\Brit\InBriefs.pdf", ExportFormat:=wdExportFormatPDF, OpenAfterExport:=True
    
    End With

End With

WdApp.Visible = False

Set WdApp = Nothing

Sheets("Dashboard").Select

Application.StatusBar = False
Application.ScreenUpdating = True
    
End Sub

And here is a sample row of the data that is being exported:

Digital 9 Infrastructure plc

Digital 9, or D9 as it is known, is named after Sustainable Development Goal 9: Industry, Innovation and Infrastructure. It is an infrastructure trust that invests in a portfolio of subsea fibre cables, data centres, terrestrial fibre, and wireless networks, including 5G. The fund is managed by Triple Point, an asset manager with experience of over $250bn in digital infrastructure transactions. D9 invests in assets which underpin both the internet and global communications: 98% of the world’s data is carried by fibre cables, but only 60% of the required trans-Atlantic subsea capacity is expected to be in place by 2026. The fund’s flagship asset is a £160m investment in AquaComms, a company which operates 20,000km of modern, operational trans-Atlantic subsea fibre cables. D9 raised £300m from investors via IPO in March 2021.

SOCIAL PERFORMANCE Digital infrastructure is crucial to serve the growing demand for data in an increasingly digital world. Triple Point, the fund’s manager, is a signatory to the PRI and embeds ESG criteria into each stage of its investment process. It is applying to become a certified B Corporation, which is awarded to companies meeting the highest standards of verified social and environmental performance, transparency and accountability. Triple Point is also a member of the Sustainable Digital Infrastructure Alliance (SDIA), an independent alliance of stakeholders in the digital sector who have committed to executing a ‘Roadmap to a Sustainable Digital Economy’ by 2030. In assessing investment in an asset, the manager seeks assurance of good customer and stakeholder relations, including management of land rights and social inclusion through access to the asset. It also examines data security, client data protection and associated risks. Strong management and reporting of health and safety (during and after build) is also expected, as well as good labour management including staff wellbeing, good diversity and inclusion practices, appropriate training, and presence of fair pay, including assurance on the absence of modern slavery. ENVIRONMENTAL PERFORMANCE The manager has robust plans in place for decarbonisation of digital infrastructure energy use and is targeting net zero emissions from its data centres, which form part of a sector that represents one of the world’s fastest growing consumers of energy. Investee companies are assessed using both SASB and SDIA frameworks, as well as being assessed against the TCFD framework. It also considers the biodiversity and habitat implications of its assets, as well as looking at levels of waste generated, avoided and disposed of, and the approach to raw material sourcing and supply chain sustainability. While expecting a minimum level of ESG performance, the fund also works with companies to improve performance through engagement.

Any help to optimise the macro and speed it up (ideally to under 5 mins) would be greatly appreciated.

Thanks!

 

[attikuz]

That has done the trick! Thanks for your help! !

 

[Anthony47]

Thank you for the feedback