Macro to Autofit Cols Page Preview

[howard]

I have the following Code below to Set the Page Layout from Sheet BR1 South to Last Sheet

The code works well, except that I need t autofit the columns as they display ##### in Page Preview Mode

It would be appreciated if someone could amend my code

 Sub SetPageSetupFromBR1SouthToLastSheet()

Dim wb As Workbook

Dim ws As Worksheet

Dim StartProcessing As Boolean

Dim printArea As String

Dim col As Range



' Optimization settings

Application.ScreenUpdating = False

Application.EnableEvents = False

Application.DisplayAlerts = False

Application.Calculation = xlCalculationManual

Application.PrintCommunication = False ' Speeds up print-related tasks



' Set the workbook

Set wb = ThisWorkbook



' Initialize variables

StartProcessing = False

printArea = "A1:L40"



' Loop through sheets

For Each ws In wb.Sheets

' Start processing only from "B11 South."

If StartProcessing Or ws.Name = "BR1 South." Then

StartProcessing = True



' Only apply settings if A1 is not blank

If Not IsEmpty(ws.Range("A1").Value) Then

' Page setup adjustments

With ws.PageSetup

.Orientation = xlLandscape ' Landscape orientation

.printArea = printArea ' Set print area

.PrintGridlines = False ' Remove gridlines

.Zoom = False ' Use scaling

.FitToPagesWide = 1 ' Fit to one page wide

.FitToPagesTall = 1 ' Fit to one page tall

End With



' Autofit columns

ws.Columns.AutoFit



' Add buffer to column widths

For Each col In ws.UsedRange.Columns

col.ColumnWidth = col.ColumnWidth + 1 ' Add a small buffer

Next col

End If

End If

Next ws



' Restore application settings

Application.Calculation = xlCalculationAutomatic

Application.PrintCommunication = True

Application.ScreenUpdating = True

Application.EnableEvents = True

Application.DisplayAlerts = True



MsgBox "Page setup and column autofit (with buffer) applied from 'BR1 South.' to the last sheet.", vbInformation

End Sub

 

[Logit]

Try :

ws.Cells.EntireColumn.AutoFit

 

[howard]

Thanks for the Reply and your input. The Col Width in normal view is perfect but on page Preview mode some cols display #####

 

[Logit]

Not certain what is happening for you. Obviously the autofit should work regardless.

 

[Joe4]

Thanks for the Reply and your input. The Col Width in normal view is perfect but on page Preview mode some cols display #####

If you manually expand those columns, does it show the correct value?
What exactly is in these cells, hard-coded values or formulas?
If formulas, what do the formulas look like?
Lastly, what font type and font size are you using?

 

[howard]

Hi guys

I managed to sort out the code so that the columns are autofitted in Normal and Page Break Preview Mode

Macro a bit slow but otherwise works perfectly

 Sub SetPageSetupFromBR1SouthToLastSheet()

Dim wb As Workbook

Dim ws As Worksheet

Dim StartProcessing As Boolean

Dim printArea As String



' Optimization settings

Application.ScreenUpdating = False

Application.EnableEvents = False

Application.DisplayAlerts = False

Application.Calculation = xlCalculationManual

Application.PrintCommunication = False ' Speeds up print-related tasks



' Set the workbook

Set wb = ThisWorkbook



' Initialize variables

StartProcessing = False

printArea = "A1:L40"



' Loop through sheets

For Each ws In wb.Sheets

' Start processing only from "BR1South"

If StartProcessing Or ws.Name = "BR1South" Then

StartProcessing = True



' Only apply settings if A1 is not blank

If Not IsEmpty(ws.Range("A1").Value) Then

' Autofit columns in range A1:L40

With ws.Range("A1:L40").Columns

.AutoFit

.ColumnWidth = .ColumnWidth + 1 ' Add a buffer to all columns

End With



' Page setup adjustments (minimized)

With ws.PageSetup

If .Orientation <> xlLandscape Or .FitToPagesWide <> 1 Or .FitToPagesTall <> 1 Then

.Orientation = xlLandscape ' Landscape orientation

.printArea = printArea ' Set print area

.Zoom = False ' Use scaling

.FitToPagesWide = 1 ' Fit to one page wide

.FitToPagesTall = 1 ' Fit to one page tall

End If

End With

End If

End If

Next ws



' Restore application settings

Application.Calculation = xlCalculationAutomatic

Application.PrintCommunication = True

Application.ScreenUpdating = True

Application.EnableEvents = True

Application.DisplayAlerts = True



' Select Macro sheet and notify user

Sheets("Macro").Select

MsgBox "Macro Completed", vbInformation

End Sub

 

[Logit]

Glad you have an answer.