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:
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.