Hello all,
Good day, love the new look for the forum.
hope this is not repeated question as I could not find the similar one.
I have an excel with an automated update from SharePoint list, this list provides me data from column A to G (worksheet named as Master Table). The data that I have in column F is generate as text which is most of the time having lot of characters and its turn the cell height become maximum number that excels can provide (409.5) this condition cause a problem that every time I save the file to pdf some of the text is cut and missing from the preview.
is there any VBA code that could split the text in column F whenever the height is maximum so that the rest of the text will go to the next row.
I used the autofit function but it is not enough to cover the needs,
can someone help me out?
here is my code with autofit :
- Sub CallMacros()
- ActiveWorkbook.Unprotect "XXX"
- Application.ScreenUpdating = False
- Dim User As String
- Dim PdfFile As String
- Dim strFile As String
- Dim strPath As String
- ThisWorkbook.RefreshAll
- Application.CalculateFull
- 'PDF Report Export
- ThisWorkbook.Sheets("Master Table").Visible = True
- ThisWorkbook.Sheets("Master Table").Select
- Range("A:G").Select
- With Selection.Font
- .Name = "Tahoma"
- .Size = 12
- .Strikethrough = False
- .Superscript = False
- .Subscript = False
- .OutlineFont = False
- .Shadow = False
- .Underline = xlUnderlineStyleNone
- .ThemeColor = xlThemeColorLight1
- .TintAndShade = 0
- .ThemeFont = xlThemeFontNone
- End With
- Range("A:G").Font.Size = 10.5
- Range("A:G").Font.Name = Tahoma
- Worksheets("Master Table").Range("A:G").EntireRow.AutoFit
- Worksheets("Master Table").Range("A:G").EntireRow.VerticalAlignment = xlVAlignCenter
- Worksheets("Master Table").Range(Columns(10), Columns(10)).EntireColumn.Hidden = True
- Worksheets("Master Table").Columns("C").Replace _
- What:="TRUE", Replacement:="YES", _
- SearchOrder:=xlByColumns, MatchCase:=True
- Worksheets("Master Table").Columns("C").Replace _
- What:="FALSE", Replacement:="NO", _
- SearchOrder:=xlByColumns, MatchCase:=True
- ThisWorkbook.Sheets("Master Table").Select
- Worksheets("Master Table").Cells(1, 1).Select
- User = Environ("username")
- strFile = Format(Int(Now()) - 3, "DD") & "-" & Format(Int(Now()), "DDMMM") & ", " & "Shift Report " & ".pdf"
- strPath = GetDesktop() & "\"
- ActiveSheet.PageSetup.CenterHeader = "&""Tahoma,bold""&12" & Chr(10) & Format(Int(Now()) - 3, "DDDD,DD/MMMM/YYYY HH:MM") & " - " & Format(Now(), "DDDD,DD/MMMM/YYYY HH:MM")
- Application.DisplayAlerts = False
- ActiveSheet.ExportAsFixedFormat _
- Type:=xlTypePDF, _
- Filename:=strPath & strFile, _
- Quality:=xlQualityStandard, _
- IncludeDocProperties:=True, _
- IgnorePrintAreas:=False, _
- OpenAfterPublish:=True
- Application.DisplayAlerts = True
- Worksheets("Master Table").Range(Columns(10), Columns(10)).EntireColumn.Hidden = False
- ActiveSheet.PageSetup.CenterHeader = ""
- ActiveSheet.AutoFilter.Sort.SortFields.Clear
- MsgBox "XXX Report was saved to your Desktop.", vbInformation
- If ActiveSheet.FilterMode Then
- ActiveSheet.ShowAllData
- End If
- Application.ScreenUpdating = True
- ThisWorkbook.Sheets("Master Table").Visible = xlSheetHidden
- ActiveSheet.Protect "XXX"
- ActiveWorkbook.Protect "XXX", True, False
- Application.ScreenUpdating = True
- End Sub