Printing data from different sheets using VBA in Microsoft Excel

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

Logic explanation

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.

Code explanation

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

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

The above Select statement is used to select the specified area based on the type defined by the user.

ActiveWindow.SelectedSheets.PrintOut

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

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

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 info@exceltip.com