When saving data on Excel sheets it is a good practice to set print regions. Otherwise, we will end up with a huge task of adjusting column positions. Using Excel macro, you can easily set print regions for all your worksheets. Let’s learn about that in this article.

Download Now

If you want to start to use the software as soon as possible, then you can:

Download the Software Now

Otherwise, if you want to DIY, you can read the contents below.

Excel printing – not a rocket science

Many people get frustrated while printing their excel spreadsheets, especially if their spreadsheets are too wide or too tall to fit on a single page. If you already know that the excel document would be printed, It is a good practice to design your excel spreadsheets before adding content to it. Using the Scaling option, you can design your excel sheet and check how you want it to be printed. Page breaks always make Excel sheets printer friendly.

Let’s Prepare the GUI

The script can be copied into a new module of any Excel workbook for which the print area has to be set. However, to save time and efforts, you can copy the script into a new macro enabled workbook. Remove unwanted sheets from that workbook and retain a single sheet which can be renamed as “GUI”. As shown in the image, create a button to allow the user to browse and select the excel workbook.

Let’s make it functional

After copying the script into a new module, attach the script “P_fpick” to the button “Browse” and the script “DrawPrintArea” should be attached to the button “Set Print Area”.

How does it work?

The procedure “P_fpick” would allow the user to browse and select an Excel file. The script “DrawPrintArea” would read the file path from the sheet “GUI” and open it. For each worksheet in the opened workbook, the script would identify the used range and then set the print area.

Sub DrawPrintArea()
    Dim v1 As Workbook
    Dim ab As Workbook
    Set ab = ThisWorkbook
    Dim v2 As Worksheet
    Dim lastRow As Long
    Set v1 = Workbooks.Open(Range("D6").Value)
    For Each v2 In v1.Worksheets
        v2.PageSetup.PrintArea = v2.UsedRange.Address
    Next
    v1.Close True
    ab.Activate
End Sub

Sub P_fpick()
    Dim v_fd As Office.FileDialog
    Set v_fd = Application.FileDialog(msoFileDialogFilePicker)
    With v_fd
        .AllowMultiSelect = False
        .Title = "Please select the Excel workbook"
        .Filters.Clear
        .Filters.Add "Excel", "*.xls*"
        If .Show = True Then
            Range("D6").Value = .SelectedItems(1)
        End If
    End With
End Sub

Tweak it

As of now the script reads and handles a single workbook. You can modify the GUI to allow the user to list multiple workbooks. As an alternate method, you can also allow the user to browse and select a folder instead of an excel workbook. The macro has to be adjusted accordingly to read all Excel workbooks within the selected folder and subfolder. If the script is not able to set the print area, there are high chances that the sheet might be corrupted and Excel recovery is the only solution.

Author Introduction:

Nick Vipond is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including word fix and outlook recovery software products. For more information visit www.datanumen.com.