Print certain sheets if cell contains...

[afc171]

Hi guys,

I have a print button which prints VBA PrintID1 but if in cell J:J contains the word EXP I want to run VBA PrintEXPForm. How would I do this?

Sub PrintID1()

Dim Ref

Ref = Sheets("Goods-In").Range("AV6").Value

   Application.ScreenUpdating = False
   With Sheets("BRCodeCheck")
      .Visible = True
      .PrintOut Copies:=1, Collate:=True
      .Visible = False
   End With
   Application.ScreenUpdating = False
   
    MsgBox "Label Form Printed >" & Ref

Call ClearGI

End Sub

Sub PrintEXPForm()

'EXCEPTIONPRODS - Prints EXP label form

Dim Ref

Ref = Sheets("ExceptionProds").Range("B4").Value

   Application.ScreenUpdating = False
   With Sheets("BRCodeCheckExp")
      .Visible = True
      .PrintOut Copies:=1, Collate:=True
      .Visible = False
   End With
   
   MsgBox "Label Form Printed >" & Ref

Call ClearSup2

End Sub

Thank you

 

[rollis13]

From your print button you could launch this macro that will choose which of the other two macros needs to be used. Maybe you will have to add reference to the sheet where Range J:J is to be checked. As it is it will check in the sheet that has the print button.

Sub PrintMacro()
    Dim srch   As Variant
    Set srch = Range("J:J").Find(What:="EXP", LookAt:=xlWhole)
    If srch Is Nothing Then
        Call PrintID1
    Else
        Call PrintEXPForm
    End If
End Sub

 

[afc171]

Thanks mate working fine. Cheers!

 

[rollis13]

Thanks for the positive feedback, glad having been of some help.