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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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

imback2nite
  • Question Question
Print size change using VBA.
Replies
4
Views
244
imback2nite
imback2nite
4
  • Question Question
VBA to Print Labels but Issues Selecting Printer
Replies
1
Views
93
rpaulson
rpaulson
F
  • Question Question
Printing error. Nothing is printed
Replies
2
Views
245
Flavien
F
T
  • Question Question
Printing to PDF use VBA - Was working but now different sheets are different widths
Replies
1
Views
150
Tanquen
T
JohnZ1156
  • Solved
VBA Macro to print table rows from a start date to an end date.
Replies
4
Views
306
Joe4
Joe4

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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