Skip Print Specific Sheet of group, IF Specific Cell Value is blank in that sheet.

Waleed_wwn

New Member
Joined
Apr 8, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
I have the below code to Print Some Sheets by using array code.
How to Skip Print Specific Sheet of this group, IF Specific Cell Value (D4) is blank in that sheet.

Sub Print_Some_Sheet()

Worksheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet4")).PrintOut , collate:=True

End Sub
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

severynm

Board Regular
Joined
Jan 8, 2021
Messages
64
Office Version
  1. 365
Platform
  1. Windows
Hello and welcome!

To accomplish this we need to examine each worksheet individually before printing out. Use a loop to look through each worksheet, and you want to print only if Not Isempty(ws.Range("D4").Value) (i.e. if D4 in the current worksheet is not empty or "blank").

VBA Code:
Private Sub PrintSomeSheets()
    Dim idx As Integer
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        If Not IsEmpty(ws.Range("D4").Value) Then
            ws.PrintOut
        End If
    Next
End Sub
 

Waleed_wwn

New Member
Joined
Apr 8, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
But , your code will print all sheets in workbook ( except if D4 is blank) . My question is about selection of sheets in same workbook.
Any way thanks, I found this code that works ok
VBA Code:
 [/I]
Sub Print_Some_Sheet()
Dim sh As Worksheet
Dim strName As Worksheet
Dim arrSheets As Variant
Dim arrSheetsToPrint As Variant
Dim cnt As Long
Dim idx As Long

    arrSheets = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")

    ReDim arrSheetsToPrint(LBound(arrSheets) To UBound(arrSheets))
   
    For idx = LBound(arrSheets) To UBound(arrSheets)
        Set sh = Sheets(arrSheets(idx))
        If sh.Range("D4").Value <> "" Then
            arrSheetsToPrint(cnt) = arrSheets(idx)
            cnt = cnt + 1
        End If
    Next idx
    
    If cnt > 0 Then
        ReDim Preserve arrSheetsToPrint(cnt - 1)
        Worksheets(arrSheetsToPrint).PrintOut , collate:=True
    End If
    
End Sub
[I]
 
Solution

Waleed_wwn

New Member
Joined
Apr 8, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Change sheets names and Range ( Cell ) as required
 

Watch MrExcel Video

Forum statistics

Threads
1,129,791
Messages
5,638,340
Members
417,021
Latest member
moon miner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top