In this article, you will get to know 5 examples related to set print area for multiple ranges using VBA in Excel.
How to Set Print Area for Multiple Ranges Using Excel VBA: 5 Examples
1. Print Multiple Ranges from Active Sheet On Same Page
This method can print multiple ranges from the active worksheet on the same page. It merges those ranges column-wise.
So the very first column of the second range will be adjacent to the last column of the first range.
From the following dataset of the attached picture, we will pick up B4:C10 as the first range and C4:D10 will be our second range.
So in the output, we will get the same data table like the following one.
❶ Press ALT + F11 to open the VBA Editor.
❷ Then go to Insert >> Module to create a new module.
❸ Insert the following code in the VBA Editor and Save it.
Sub Print_Multiple_Ranges_Active_Sheet()
Dim yRng1 As Range
Dim yRng2 As Range
Dim rxUni As Range
Set yRng1 = Sheets("Sheet1").Range("B4:C10")
Set yRng2 = Sheets("Sheet1").Range("C4:E10")
With ActiveSheet.PageSetup
.printArea = yRng1.Address & "," & yRng2.Address
ActiveWindow.SelectedSheets.PrintOut Preview:=Preview
End With
End Sub
Breakdown of the Code
- Here, I declared 3 variables
- Then I input two ranges.
- After that, I set PrintArea for the multiple ranges and then printed out the specified range using the With statement property.
❹ Press the F5 key to Run the code.
The printing will automatically begin. You will see the following table in the printed output.
2. Set Print Area for Multiple Ranges of a Selected Sheet
You can use this method to set multiple ranges as print areas. You can also specify a sheet’s serial number to print a specific sheet.
To get the VBA code usage steps, Click_Here.
❶ Insert the following VBA code in the VBA Editor and Save it.
Sub Print_Multiple_Ranges_Selected_Sheet()
Worksheets(2).Range("B4:C10, B4:D10").PrintOut
End Sub
Breakdown of the Code
- Here, I’ve specified the worksheet number using the Worksheets()
- Then I used the Range property to specify my ranges.
- Finally, used the PrintOut command to start printing.
❷ Now press the F5 key to Run the code.
The printing will automatically begin.
And you will see the contents of the inserted ranges on separates pages like this:
3. Print Multiple Ranges on a Single Page by Merging Rows
You can use this method to set multiple ranges as print areas and print them all on the same page.
To get the VBA code usage steps, Click_Here.
❶ Insert the following VBA code in the VBA Editor and Save it.
Sub Print_Multiple_Range_in_One_Page()
Dim yRng1 As Range
Dim yRng2 As Range
Dim yNewWs As Worksheet
Dim yWs As Worksheet
Dim yIndex As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set yWs = ActiveSheet
Set yNewWs = Worksheets.Add
yWs.Select
yIndex = 1
For Each yRng2 In Selection.Areas
yRng2.Copy
Set yRng1 = yNewWs.Cells(yIndex, 1)
yRng1.PasteSpecial xlPasteValues
yRng1.PasteSpecial xlPasteFormats
yIndex = yIndex + yRng2.Rows.Coun
Next
yNewWs.Columns.AutoFit
yNewWs.PrintOut
yNewWs.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Breakdown of the Code
- First, I declared 5 variables.
- Then I used the Add property to open a new worksheet.
- I used a For loop to Copy each of the selected areas and Paste their values as well as formats to the new worksheet sequentially.
- After that, I used the AutoFit property to fit the pasted values perfectly.
- I used the PrintOut property to print the contents of the newly added worksheet.
- Finally, I used the Delete property to delete the worksheet.
❷ Go back to your worksheet and select the range that you want to print out.
❸ After that, press ALT + F8 to open the Macro dialog box.
❹ Select the macro Print_Multiple_Range_in_One_Page and hit the Run button.
The printing will automatically begin. You will get all your selected ranges printed on the same page like this:
4. Set Multiple Ranges From Multiple Sheets as Print Area
To set multiple ranges as the print areas from multiple sheets, you can follow this method.
For getting the VBA code usage steps, Click_Here.
❶ Insert the following VBA code in the VBA Editor and Save it.
Sub Print_Multiple_Ranges_from_Multiple_Sheets()
Dim xWs As Worksheet
For Each xWs In Worksheets(Array("Sheet1", "Sheet2"))
With xWs
With .PageSetup
.printArea = "$B$4:$C$10,$B$4:$D$10"
.FitToPagesTall = 2
.FitToPagesWide = 1
End With
.PrintOut Copies:=1
End With
Next
End Sub
Breakdown of the Code
- Here, I declared a variable first.
- Then I used a For loop to retrieve each sheet.
- I also used the printArea property to set multiple ranges using the With statement.
- Finally, I defined the print copy as 1 using the PrintOut Copies
❷ Now press the F5 key to Run the code.
The printing will automatically begin.
And you will see the contents of the inserted ranges on separates pages like this:
Read More: How to Print Specific Sheets Using VBA Macro in Excel
5. Print Multiple Named Ranges on a Single Page by Merging Columns
If you have too many ranges to print out, you can name each of them uniquely for convenience.
In this method, you will learn to print multiple named ranges on the same page by merging columns together.
From the following dataset of the attached picture, we will pick up B4:C10 as the first range and C4:D10 will be our second range.
So in the output, we will get the same data table as the following one.
To get the VBA code usage steps, Click_Here.
❶ Insert the following VBA code in the VBA Editor and Save it.
Sub Print_Multiple_Named_Range()
Dim xRng As Range
Dim yRng As Range
Set xRng = Range("$B$4:$C$10")
Set yRng = Range("$D$4:$E$10")
With ActiveSheet
.PageSetup.printArea = Union(xRng, yRng).Address
ActiveWindow.SelectedSheets.PrintOut Preview:=Preview
End With
End Sub
Breakdown of the Code
- First, I declared two variables.
- Then I assigned cell ranges to different names.
- After that, I used the Union function to merge those ranges column-wise to set the PrintArea .
- Finally, I used the PrintOut property to print out the specified ranges.
❷ Now, go back to your worksheet first.
❸ After that, press ALT + F8 to open the Macro dialog box.
❹ Select the macro Print_Multiple_Named_Range and hit the Run button.
The printing will automatically begin. You will get all your selected ranges printed on the same page like this:
Practice Section
You will get an Excel sheet like the following screenshot, at the end of the provided Excel file. Where you can practice all the methods discussed in this article.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
Conclusion
To sum up, we have discussed 5 examples to set print area for multiple ranges using VBA in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap.
Related Articles
- Excel VBA: Print Preview for Selected Range
- How to Display Print Preview with Excel VBA
- Excel VBA Debug Print: How to Do It?
- Excel VBA: How to Set Print Area Dynamically
- How to Set Print Area to Selection Using VBA in Excel
- VBA Code for Print Button in Excel
- Excel VBA: Print Range of Cells
- Excel VBA to Display Print Preview for Multiple Sheets
how can I change the value in multiple cell,
For example,
i have data of 20 student’s like
1. Name, 2. Roll Number, 3. subject and 4. semester. and this all data is on sheet 2, and I want to print each student’s data automatically in a same format front page.
Hello HBING,
To solve your issue follow the steps given below.
• Firstly, go to the Page Layout tab >> click on Print Titles.
• After that, type 5:5 as Print Area and $2:$2 as Rows to repeat at top.
• Then, click on OK.
• Now, write the following code in your module.
Sub Print_Each_Data()
Dim iRng As RangeDim iWorkRng As RangeDim ixWs As WorksheetOn Error Resume NextxTitleId = "Microsoft Excel"Set iWorkRng = Application.SelectionSet iWorkRng = Application.InputBox("Range", xTitleId, iWorkRng.Address, Type:=8)Set ixWs = iWorkRng.ParentFor Each iRng In iWorkRngixWs.PageSetup.PrintArea = iRng.EntireRow.AddressixWs.PrintPreviewNextEnd Sub• Next, click on Macros from the Developer tab.
• Select the macro named Print_Each_Data.
• Lastly, click on Run.
• Now, a box will open.
• Then, select the range which you want to print. Here, we selected cell range B2:E3.
• Finally, click on OK.
• Thus, you can print each student’s data automatically in the same format.
If you face any further problems, please share your Excel file with us in the comment section.
Regards
Arin Islam,
Exceldemy.