Selective sheet printing

M

Mutley247

New Member
Joined
May 22, 2024
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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 1X
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
 
Sort by date Sort by votes
Inside the PRINTING (selected) Contamination section, the sheet name is the same both times. Is one of these supposed to be something else?

The quick way to modify what you already have is:
VBA Code: 
Private Sub CommandButton2_Click()
For i = 1 To 5
    '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
Next
End Sub

The streamlined version would be:
VBA Code: 
Private Sub CommandButton2_Click()

    With Sheets("EMFILTER")
        If .Range("AL4").Value > "" Then
            ContamSheet = "**** Contamination"
        ElseIf .Range("AL5").Value > "" Then
            ContamSheet = "**** Contamination"
        'Else
        End If
    End With

    Application.ScreenUpdating = False
   
    Sheets(Array("EMDOC", "EMFILTER", ContamSheet)).Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=5, Collate:=True
   
    Worksheets("Home ").Activate
    Application.ScreenUpdating = True

End Sub
 
Upvote 0

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
NateSC said:
Inside the PRINTING (selected) Contamination section, the sheet name is the same both times. Is one of these supposed to be something else?

The quick way to modify what you already have is:
VBA Code: 
Private Sub CommandButton2_Click()
For i = 1 To 5
    '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
Next
End Sub

The streamlined version would be:
VBA Code: 
Private Sub CommandButton2_Click()

    With Sheets("EMFILTER")
        If .Range("AL4").Value > "" Then
            ContamSheet = "**** Contamination"
        ElseIf .Range("AL5").Value > "" Then
            ContamSheet = "**** Contamination"
        'Else
        End If
    End With

    Application.ScreenUpdating = False
  
    Sheets(Array("EMDOC", "EMFILTER", ContamSheet)).Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=5, Collate:=True
  
    Worksheets("Home ").Activate
    Application.ScreenUpdating = True

End Sub
Click to expand...
I didn't sensor that very well did I? :unsure:
Should be (A Contamination) & (B Contamination)

I cant really
 
Upvote 0
NateSC said:
Inside the PRINTING (selected) Contamination section, the sheet name is the same both times. Is one of these supposed to be something else?

The quick way to modify what you already have is:
VBA Code: 
Private Sub CommandButton2_Click()
For i = 1 To 5
    '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
Next
End Sub

The streamlined version would be:
VBA Code: 
Private Sub CommandButton2_Click()

    With Sheets("EMFILTER")
        If .Range("AL4").Value > "" Then
            ContamSheet = "**** Contamination"
        ElseIf .Range("AL5").Value > "" Then
            ContamSheet = "**** Contamination"
        'Else
        End If
    End With

    Application.ScreenUpdating = False
  
    Sheets(Array("EMDOC", "EMFILTER", ContamSheet)).Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=5, Collate:=True
  
    Worksheets("Home ").Activate
    Application.ScreenUpdating = True

End Sub
Click to expand...
Im getting run_time Error '1004' on line Sheets(Array("EMDOC", "EMFILTER", ContamSheet)).Select
 
Upvote 0
I think your problem is with the name of the Contamination sheet:
VBA Code: 
ContamSheet = "**** Contamination"
I don't think you can have **** in the tab name. If you put those in as a placeholder, you will need to update that information. I changed the **** to something else and the shorter macro worked.

Here's the exact macro I used below. I changed it to a regular sub because I didn't have a form and button to trigger the action.
VBA Code: 
Sub TestPrint2()

    With Sheets("EMFILTER")
        If .Range("A4").Value > "" Then
            ContamSheet = "1234 Contamination"
        ElseIf .Range("A5").Value > "" Then
            ContamSheet = "2345 Contamination"
        'Else
        End If
    End With

    Application.ScreenUpdating = False
   
    Sheets(Array("EMDOC", "EMFILTER", ContamSheet)).Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=5, Collate:=True
   
    'Worksheets("Home ").Activate

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Print an Array to PDF only the tabs if Value in Cell is Greater than 0
Replies
4
Views
211
ExcelError
E
I
  • Solved
Print sheets based on checkbox
Replies
3
Views
433
John_w
J
A
  • Solved
VBA if cancelled don't print otherwise print
Replies
2
Views
188
afc171
A
E
  • Solved
Option buttons not clickable after running VBA export code
Replies
2
Views
160
Domenic
D
JohnZ1156
  • Solved
Print only visible Filtered Rows from Excel Table
Replies
2
Views
415
JohnZ1156
JohnZ1156

Forum statistics

Threads
1,217,414
Messages
6,136,492
Members
450,016
Latest member
murarj

Share this page

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
Back
Top