This post may contain affiliate links. Please read my disclosure for more info.

Prepare a report for distribution using VBA

Excel VBA Prepare Report For Distribution


Do you ever have to distribute reports as Excel files?  Sometimes PDF just doesn’t cut it, especially if a file is meant to be interactive.  But if we just save the file and e-mail it without considering the readability for the recipients, our beautiful spreadsheet can so easily miss the mark.  If you have not optimized the spreadsheet as a report to be viewed it can give the recipient poor user experience.


Here are some of the common pitfalls

  • Page break preview is turned on, giving ugly blue lines all over your workbook
  • The worksheet was saved with the scroll position too far down the worksheet
  • The workings section of the spreadsheet is displayed, which makes your report look nothing like a report and more like . . . well . . . workings!
  • The grid lines, column letters and row numbers are displayed, which isn’t required if it is a report.
  • The first page the recipients sees is the page you saved the file on, not the first page of the report.

Oh dear, you’re the lovely report, now looks a bit rubbish.  It can be very time consuming to go through each worksheet and optimize it for presentation.  So, why not create a macro which can do that for you.  It will make sure that your reports look as good as they can.



The VBA Code


This macro will loop through every worksheet and ‘optimize’ it for presentation.

Sub OptimizeReportView()

'Declare the worksheet variable
Dim Ws As Worksheet

'Loop through each worksheet to prepare it for distribution
For Each Ws In Worksheets
    'Close all of groups 
    Ws.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1

    'Set the view settings to normal - looks better than print preview
    ActiveWindow.View = xlNormalView
    'Remove the gridlines
    ActiveWindow.DisplayGridlines = False

    'Remove the headings on each of the worksheets
    ActiveWindow.DisplayHeadings = False
    'Get worksheet to display top left
    Ws.Cells(1, 1).Select
Next Ws

'Find the first visible worksheet and select it
For Each Ws In Worksheets
    If Ws.Visible = xlSheetVisible Then
        'Select the first visible worksheet
        'Once the first visible worksheet is found exit the sub
        Exit For
    End If
Next Ws
End Sub