Forum Discussion

dshawSLDC's avatar
dshawSLDC
Brass Contributor
Jun 25, 2021

Print macro to check if data is there before printing

Hello,

So I am using a macro to print specific sheets in a workbook. These sheets are filled based on a let Formula. I am trying to figure out hot to make it check if there is data before it prints. this is what I currently have it works well the only down side is that it prints all sheets even if there is no data.

 

Thanks for any assitance.

 

Sub PrintAllDistricts()
    Dim xRg As Range
    Dim xCell As Range
    Dim xRgVList As Range
    Set xRg = Worksheets("Report Select").Range("B1")
    Set xRgVList = Evaluate(xRg.Validation.Formula1)
    For Each xCell In xRgVList
        xRg = xCell.Value
        Call Module1.PrintDistrict
    Next
End Sub

 

 

Here is the module it calls.

 

 

Sub PrintDistrict()
Sheets(Array("Page1", "Page2")).Select
Sheets("Page1").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets(Array("Page1", "Page2")).Select
Sheets("Report Select").Activate
Range("B1").Select
End Sub

 

 

Also attached is an example.

 

excel
Macros and VBA
office 365

1 Reply

  • dshawSLDC's avatar
    dshawSLDC
    Brass Contributor
    Jun 28, 2021

    This is what Im working with now but I am getting errors.

    Sub check()
With Sheets("invoice pg.2")
    If WorksheetFunction.CountA(Sheets("Invoice pg.2").Range("B14")) = 0 Then
        Sheets("Invoice").Select
        Sheets("Invoice").Activate
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Else
        With Sheets("invoice pg.3")
            If WorksheetFunction.CountA(Sheets("Invoice pg.3").Range("B14")) = 0 Then
                 Sheets(Array("Invoice", "Invoice pg.2")).Select
                    Sheets("Invoice").Activate
                        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Else
        With Sheets("invoice pg.4")
            If WorksheetFunction.CountA(Sheets("Invoice pg.4").Range("B14")) = 0 Then
                 Sheets(Array("Invoice", "Invoice pg.2", "Invoice pg.3")).Select
                    Sheets("Invoice").Activate
                        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Else
                Sheets(Array("Invoice", "Invoice pg.2", "Invoice pg.3", "invoice pg.4")).Select
                    Sheets("Invoice").Activate
                        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
                        Sheets(Array("Invoice", "Invoice pg.2", "Invoice pg.3", "Invoice pg.4")).Select
                        
    End If
    End If
    End If
    
End With
End Sub

     

    Can anyone point me in the right direction?

Resources