Selective sheet printing

[Mutley247]

Hi,
I know this topic may have been covered before (I just cant find it im afraid)

I have a command button that is printing a number of sheets,

I have a table that tells me a sheet that needs to be printed based on what is selected ("X")

Sheet 1	X
Sheet 2

Below is what I'm using to print the basic sheets that need to print regardless,

Private Sub CommandButton2_Click()
'----------------------------------------PRINTING
Application.ScreenUpdating = False
With Sheets("contamination")
.Visible = True
.PrintOut Copies:=1, collate:=True
.Visible = True
End With
Application.ScreenUpdating = True
Worksheets("contamination").Activate
CON_PRINT.Hide
Worksheets("SG Tracker").Visible = False
Exit Sub


What can I use to select what sheet (Sheet1 / sheet 2) based on cells ("AL4) or ("AL5") containing "X"

if
sheet EMFILTER.range ("AL4" ="X") print sheet1

sheet EMFILTER.range("AL5" = "X") print sheet2

 

[NateSC]

Just assign the sheet name to a variable depending on which cell has the X. For error proofing, consider what happens if neither has an X or if both have an X. Once you have the variable defined, you use the same code you used above to print the "contamination" sheet. (I don't think you need 2 visible statements but perhaps one is supposed to be set to False - just a thought.)

If Sheets("EMFILTER").Range("AL4").Value = UCase("X") Then
    ShtToPrnt = "Sheet1"
ElseIf Sheets("EMFILTER").Range("AL5").Value = UCase("X") Then
    ShtToPrnt = "Sheet2"
'Else 'What if neither has an X or if both have an X????
End If

With Sheets(ShtToPrnt)
    .Visible = True
    .PrintOut Copies:=1, collate:=True
    .Visible = True
End With

 

[Mutley247]

I shall give this a go with with error proofing, but at the moment Im running testing with this and so far seams to be working.

Thank you for your help.

With Sheets("EMFILTER")
        If .Range("AL4").Value > "" Then
            Application.ScreenUpdating = False
With Sheets("Avgas Contamination")
.Visible = True
.PrintOut Copies:=1, collate:=True
.Visible = False
End With
Application.ScreenUpdating = True
Worksheets("Home ").Activate
            
            
        ElseIf .Range("AL5").Value > "" Then
             Application.ScreenUpdating = False
With Sheets("JET A-1 Contamination")
.Visible = True
.PrintOut Copies:=1, collate:=True
.Visible = False
End With
Application.ScreenUpdating = True
Worksheets("Home ").Activate

 

[jolivanes]

Re: "using to print the basic sheets that need to print regardless," What does this mean? Is that for sheets to be printed beside the ones marked with an "X"?

 

[Mutley247]

Private Sub CommandButton2_Click()
'----------------------------------------PRINTING EMDOC
Application.ScreenUpdating = False
With Sheets("EMDOC")
.Visible = True
.PrintOut Copies:=1, collate:=True
.Visible = True
End With
Application.ScreenUpdating = True
''Worksheets("Home ").Activate
'Emergency.Hide

'----------------------------------------PRINTING EMFILTER
Application.ScreenUpdating = False
With Sheets("EMFILTER")
.Visible = True
.PrintOut Copies:=1, collate:=True
.Visible = True
End With
Application.ScreenUpdating = True
'Worksheets("Home ").Activate
'Emergency.Hide


'----------------------------------------PRINTING (selected) Contamination

 With Sheets("EMFILTER")
        If .Range("AL4").Value > "" Then
            Application.ScreenUpdating = False
With Sheets("Avgas Contamination")
.Visible = True
.PrintOut Copies:=1, collate:=True
.Visible = False
End With
Application.ScreenUpdating = True
Worksheets("Home ").Activate
            
            
        ElseIf .Range("AL5").Value > "" Then
             Application.ScreenUpdating = False
With Sheets("JET A-1 Contamination")
.Visible = True
.PrintOut Copies:=1, collate:=True
.Visible = False
End With
Application.ScreenUpdating = True
Worksheets("Home ").Activate
      
            
      Else
            
        End If
    End With


'Input clear doc's
'input clear fields
'clear cells
'hide form


End Sub

Re: "using to print the basic sheets that need to print regardless," What does this mean? Is that for sheets to be printed beside the ones marked with an "X"?

Sheet 3 & sheet 4 print regardless,
but printing sheet 1 or sheet 2 will depend on what value is selected

 

[Mutley247]

So I have gotten this working now,
The sheets that are needed are printing as needed.

My next question is,
As the 3 print jobs combine a pack of documents, How can I get this to run multiple times?
I need these 3 jobs to print out in order five times.

 

[NateSC]

The easiest way would be to set a loop and run it 5 times. It might not be the most efficient, but it should work. You could also make the 5 a variable it reads from somewhere if there are times you only want 3 or 4 packets. That way you could set the value on the sheet somewhere and adjust as needed.

For i = 1 to 5
    'Do stuff here
Next

 

[Mutley247]

The easiest way would be to set a loop and run it 5 times. It might not be the most efficient, but it should work. You could also make the 5 a variable it reads from somewhere if there are times you only want 3 or 4 packets. That way you could set the value on the sheet somewhere and adjust as needed.

For i = 1 to 5
    'Do stuff here
Next

Thank you,
Being so new to VBA still, please excuse my ignorance with the next question.

with regards to adding the above, how would I add this?

at the moment I have the command button set to print the 3 documents

"
Private sub commandButton2_Click()
(Print sheet 1 script)
'--------------------------------------
(Print Sheet 2 Scrips)
'--------------------------------------
(Print Sheet 3 Scrips)
'------------------------------------
Else
End if
End With
End Sub
"

 

[NateSC]

The easiest way would be:

Private sub commandButton2_Click()
For i = 1 to 5
    (Print sheet 1 script) 
    '--------------------------------------
    (Print Sheet 2 Scrips) 
    '--------------------------------------
    (Print Sheet 3 Scrips) 
    '------------------------------------
    Else 
    End if
    End With
Next
End Sub

You could make this faster by selecting all the sheets you want to print at once and only sending one print command. Then you could tell it to print 5 copies. Since it only sends the info to the printer once, it should be faster than multiple print jobs and the printer is likely your slow point in the code:

Sheets(Array("Sheet1", "Sheet3", "Sheet4")).Select
Sheets("Sheet1").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=5, Collate:=True, _
IgnorePrintAreas:=False

 

[Mutley247]

I could look at this indeed.
Being on a works laser printer, I do end of month sheets report print outs, but never really encountered any noticeable delay in the print jobs.

This new script Ive set up the print jobs to print in a specific order,
Sheet 1
sheet 2
sheet3 (or 4 pending on a cell selection)

This prints out as an investigation documentation pack.
sheet 1 = Declaration / confirmation / testing confirmation (3 pages)
sheet 2 = recorded history of what is being investigated (4 pages)
sheet 3 / 4 (pending on selection) = testing parameter record (1 sheet)

These need to print out in the exact order for the outside authority investigations,
so running it 5 times would ensure the ordering as needed.

This is what Ive done so far before adding your advised addition

Private Sub CommandButton2_Click()

'PRINT 1===============================================================================
'----------------------------------------PRINTING EMDOC
Application.ScreenUpdating = False
With Sheets("EMDOC")
.Visible = True
.PrintOut Copies:=1, collate:=True
.Visible = True
End With
Application.ScreenUpdating = True
'----------------------------------------PRINTING EMFILTER
Application.ScreenUpdating = False
With Sheets("EMFILTER")
.Visible = True
.PrintOut Copies:=1, collate:=True
.Visible = True
End With
Application.ScreenUpdating = True
'----------------------------------------PRINTING (selected) Contamination
 With Sheets("EMFILTER")
        If .Range("AL4").Value > "" Then
            Application.ScreenUpdating = False
With Sheets("**** Contamination")
.Visible = True
.PrintOut Copies:=1, collate:=True
.Visible = False
End With
Application.ScreenUpdating = True
Worksheets("Home ").Activate
ElseIf .Range("AL5").Value > "" Then
Application.ScreenUpdating = False
With Sheets("**** Contamination")
.Visible = True
.PrintOut Copies:=1, collate:=True
.Visible = False
End With
Application.ScreenUpdating = True
Worksheets("Home ").Activate
                  
 Else
          
 End If
 End With

End Sub