In this article, we will create a macro to print data from different sheets.
We have different raw datas in two sheets and we want to customize print-out from these sheets.
Before running the macro, we have to specify the type of print-out required and specify the name, range or custom-view.
We have created a custom-view “customView1”.
We have created “PrintReports” macro for customized printing. This macro will loop starting from cell A13 to the last row. We can specify three different types for customized printing.
For type 1, we need to specify the sheet name in the next column.
For type 2, we need to specify the range for which we want the print-out.
For type 3, we need to specify the name of the custom-view.
For Each Cell1 In Range(“A13″, ActiveCell.End(xlDown))
The above code is used to loop starting from cell A13 to the last row.
DefinedName = ActiveCell.Offset(0, 1).Value
The above code is used to get the value from cell in the next column from the active cell.
Select Case Cell1.Value
‘Selecting the defined sheet
‘Selecting the defined range
‘Selecting the defined custom view
The above Select statement is used to select the specified area based on the type defined by the user.
The above code is used to print the selected area.
Please follow below for the code
Option Explicit Sub PrintReports() 'Declared variables Dim DefinedName As String Dim Cell1 As Range 'Disabling screen updates Application.ScreenUpdating = False 'Looping through all the cells For Each Cell1 In Range("A13", ActiveCell.End(xlDown)) Sheets("Main").Activate 'Selecting the cell Cell1.Select 'Getting value of sheet name or defined range DefinedName = ActiveCell.Offset(0, 1).Value Select Case Cell1.Value Case 1 'Selecting the defined sheet Sheets(DefinedName).Select Case 2 'Selecting the defined range Application.Goto Reference:=DefinedName Case 3 'Selecting the defined custom view ActiveWorkbook.CustomViews(DefinedName).Show End Select 'Printing the required data ActiveWindow.SelectedSheets.PrintOut Next Application.ScreenUpdating = True End Sub
We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at firstname.lastname@example.org