Print Select worksheets based on cell values

[BODYCOTE]

How would i write the VBA to print worksheets based on the values of column "e" in this table (table60) when i press a button

for example, hit button and current sheet (****tail converter sheet) prints

then

if E4=<0 then print worksheet"b4"! (Amaretto Sour)

and so on until the end of column E (prep)


Thank you in advance

 

[Osvaldo Palmeiro]

Hi. Try:

Sub PrintSomeSheets()
 Dim c As Range, LR As Long
  With ActiveSheet
   On Error Resume Next
   .ShowAllData
   On Error GoTo 0
   .PrintOut
   LR = .Cells(Rows.Count, 5).End(3).Row
   .Range("B3:G" & LR).AutoFilter 4, "<" & 1
   If .AutoFilter.Range.Columns(5).SpecialCells(12).Count > 1 Then
    For Each c In .Range("B4:B" & LR).SpecialCells(12)
     Sheets(c.Value).PrintOut
    Next c
   End If
   .ShowAllData
  End With
End Sub

 

[BODYCOTE]

Hi. Try:

Sub PrintSomeSheets()
 Dim c As Range, LR As Long
  With ActiveSheet
   On Error Resume Next
   .ShowAllData
   On Error GoTo 0
   .PrintOut
   LR = .Cells(Rows.Count, 5).End(3).Row
   .Range("B3:G" & LR).AutoFilter 4, "<" & 1
   If .AutoFilter.Range.Columns(5).SpecialCells(12).Count > 1 Then
    For Each c In .Range("B4:B" & LR).SpecialCells(12)
     Sheets(c.Value).PrintOut
    Next c
   End If
   .ShowAllData
  End With
End Sub

Thanks Osvaldo, however, it only printed out sheets with a value of 0 (flying grasshopper, Sex on the beach) instead of greater than 0

 

[Osvaldo Palmeiro]

Yes, that was my understanding of the criteria to print, based on your statement on post #1 ~~~> "if E4=<0 then print worksheet"b4"! (Amaretto Sour)"
But, to print if E>0 then please, change the criteria from "<" & 1 to ">" & 0

 

[BODYCOTE]

Thanks Osvaldo, however, it only printed out sheets with a value of 0 (flying grasshopper, Sex on the beach) instead of greater

Yes, that was my understanding of the criteria to print, based on your statement on post #1 ~~~> "if E4=<0 then print worksheet"b4"! (Amaretto Sour)"
But, to print if E>0 then please, change the criteria from "<" & 1 to ">" & 0

yes, thankyou. typo on my behalf. do i need to change anything else as the vba will not run now

 

[BODYCOTE]

yes, thankyou. typo on my behalf. do i need to change anything else as the vba will not run now
View attachment 38942

Thank you, i changed the '<' to '>' and restarted vba. this has worked perfectly, thankyou

 

[BODYCOTE]

Hey mate,

do you know why i am receiving this error?

 

[BODYCOTE]

Hey @Osvaldo Palmeiro,

I recently updated this worksheet to split that table into 2 tables, could you help me to adapt the vba to account for the change? greatly appreciated if you have a moment!!!

Sub PrintRecipes()
 Dim c As Range, LR As Long, lo As ListObject
 Set lo = Sheets("****tail Conversion Sheet").ListObjects(1)
  With ActiveSheet
   On Error Resume Next
   .ShowAllData
   On Error GoTo 0
   .PrintOut
   LR = .Cells(Rows.Count, 5).End(3).Row
   If Application.CountIf(.Range("E4:E" & LR), ">0") > 0 Then
   .Range("B3:G" & LR).AutoFilter 4, ">" & 0
    For Each c In .Range("B4:B" & LR).SpecialCells(12)
     Sheets(c.Value).PrintOut
    Next c
    lo.AutoFilter.ShowAllData
   End If
  End With
  Sheets("****tail Conversion Sheet").Select
End Sub

The Sheet Looks Like This Now