VBA macro to search each sheet cell for specific text and print to PDF when true

Hi guys,

I need urgent help with this VBA code, I have got it searching a cell in each worksheet ("E1"). The issue I am having is that when it gets to a sheet where the cell "E1" does not have the cell text "NO", the code stops and it will not continue on to start searching the next worksheet, it will take me to the very end worksheet of the code. I need it to search each worksheet (2-101) regardless of whether it found the word "NO" in the cell on the previously searched worksheet. The macro button is linked to "Sub Print_all_outstanding()"

Thanks guys

See below:



Public Sub Print_all_outstanding()

Dim cell As Range

Call Print_LOG_Sheet
Call Search_for_NO_2_to_10
'Call Search_for_NO_11_to_20
'Call Search_for_NO_21_to_30
'Call Search_for_NO_31_to_40
'Call Search_for_NO_41_to_50
'Call Search_for_NO_51_to_60
'Call Search_for_NO_61_to_70
'Call Search_for_NO_71_to_80
'Call Search_for_NO_81_to_90
'Call Search_for_NO_91_to_101

End Sub

Sub Print_LOG_Sheet()

Worksheets(1).Select
Call Print_RFI_LOG_sub

End Sub

Function Search_for_NO_2_to_10()

Worksheets(2).Select
Call Search_for_NO

Worksheets(3).Select
Call Search_for_NO

Worksheets(4).Select
Call Search_for_NO

Worksheets(5).Select
Call Search_for_NO

Worksheets(6).Select
Call Search_for_NO

Worksheets(7).Select
Call Search_for_NO

Worksheets(8).Select
Call Search_for_NO

Worksheets(9).Select
Call Search_for_NO

Worksheets(10).Select
Call Search_for_NO

End Function

Sub Search_for_NO_11_to_20()

Worksheets(11).Select
Search_for_NO

Worksheets(12).Select
Search_for_NO

Worksheets(13).Select
Search_for_NO

Worksheets(14).Select
Search_for_NO

Worksheets(15).Select
Search_for_NO

Worksheets(16).Select
Search_for_NO

Worksheets(17).Select
Search_for_NO

Worksheets(18).Select
Search_for_NO

Worksheets(19).Select
Search_for_NO

Worksheets(20).Select
Search_for_NO

End Sub

Sub Search_for_NO_21_to_30()

Worksheets(21).Select
Search_for_NO

Worksheets(22).Select
Search_for_NO

Worksheets(23).Select
Search_for_NO

Worksheets(24).Select
Search_for_NO

Worksheets(25).Select
Search_for_NO

Worksheets(26).Select
Search_for_NO

Worksheets(27).Select
Search_for_NO

Worksheets(28).Select
Search_for_NO

Worksheets(29).Select
Search_for_NO

Worksheets(30).Select
Search_for_NO

End Sub

Sub Search_for_NO_31_to_40()

Worksheets(31).Select
Search_for_NO

Worksheets(32).Select
Search_for_NO

Worksheets(33).Select
Search_for_NO

Worksheets(34).Select
Search_for_NO

Worksheets(35).Select
Search_for_NO

Worksheets(36).Select
Search_for_NO

Worksheets(37).Select
Search_for_NO

Worksheets(38).Select
Search_for_NO

Worksheets(39).Select
Search_for_NO

Worksheets(40).Select
Search_for_NO

End Sub

Sub Search_for_NO_41_to_50()

Worksheets(41).Select
Search_for_NO

Worksheets(42).Select
Search_for_NO

Worksheets(43).Select
Search_for_NO

Worksheets(44).Select
Search_for_NO

Worksheets(45).Select
Search_for_NO

Worksheets(46).Select
Search_for_NO

Worksheets(47).Select
Search_for_NO

Worksheets(48).Select
Search_for_NO

Worksheets(49).Select
Search_for_NO

Worksheets(50).Select
Search_for_NO

End Sub

Sub Search_for_NO_51_to_60()

Worksheets(51).Select
Search_for_NO

Worksheets(52).Select
Search_for_NO

Worksheets(53).Select
Search_for_NO

Worksheets(54).Select
Search_for_NO

Worksheets(55).Select
Search_for_NO

Worksheets(56).Select
Search_for_NO

Worksheets(57).Select
Search_for_NO

Worksheets(58).Select
Search_for_NO

Worksheets(59).Select
Search_for_NO

Worksheets(60).Select
Search_for_NO

End Sub

Sub Search_for_NO_61_to_70()

Worksheets(61).Select
Search_for_NO

Worksheets(62).Select
Search_for_NO

Worksheets(63).Select
Search_for_NO

Worksheets(64).Select
Search_for_NO

Worksheets(65).Select
Search_for_NO

Worksheets(66).Select
Search_for_NO

Worksheets(67).Select
Search_for_NO

Worksheets(68).Select
Search_for_NO

Worksheets(69).Select
Search_for_NO

Worksheets(70).Select
Search_for_NO

End Sub

Sub Search_for_NO_71_to_80()

Worksheets(71).Select
Search_for_NO

Worksheets(72).Select
Search_for_NO

Worksheets(73).Select
Search_for_NO

Worksheets(74).Select
Search_for_NO

Worksheets(75).Select
Search_for_NO

Worksheets(76).Select
Search_for_NO

Worksheets(77).Select
Search_for_NO

Worksheets(78).Select
Search_for_NO

Worksheets(79).Select
Search_for_NO

Worksheets(80).Select
Search_for_NO

End Sub

Sub Search_for_NO_81_to_90()

Worksheets(81).Select
Search_for_NO

Worksheets(82).Select
Search_for_NO

Worksheets(83).Select
Search_for_NO

Worksheets(84).Select
Search_for_NO

Worksheets(85).Select
Search_for_NO

Worksheets(86).Select
Search_for_NO

Worksheets(87).Select
Search_for_NO

Worksheets(88).Select
Search_for_NO

Worksheets(89).Select
Search_for_NO

Worksheets(90).Select
Search_for_NO

End Sub

Sub Search_for_NO_91_to_101()

Worksheets(91).Select
Search_for_NO

Worksheets(92).Select
Search_for_NO

Worksheets(93).Select
Search_for_NO

Worksheets(94).Select
Search_for_NO

Worksheets(95).Select
Search_for_NO

Worksheets(96).Select
Search_for_NO

Worksheets(97).Select
Search_for_NO

Worksheets(98).Select
Search_for_NO

Worksheets(99).Select
Search_for_NO

Worksheets(100).Select
Search_for_NO

Worksheets(101).Select
Search_for_NO

End Sub

Function Search_for_NO()

For Each cell In Range("E1")
If cell.Value = "NO" Then
Print_to_PDF_sub
Else
End If
Next

End Function

Sub Print_RFI_LOG_sub()

SheetName = "RFI RECORD SHEET - "
JobNum = Range("D2") & " - "
JobName = Range("D3")
Exten = ".pdf"
'
Range("A1:H105").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ChDir "C:\Users" & Environ("Username") & "\Downloads"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users" & Environ("Username") & "\Downloads" & SheetName & JobNum & JobName & Exten _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=True, OpenAfterPublish:=False
With ActiveSheet.PageSetup
.PrintArea = myRange
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.CenterHorizontally = True
.CenterVertically = False
.PaperSize = xlPaperA4
.BlackAndWhite = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
Application.PrintCommunication = True

End Sub

Sub Print_to_PDF_sub()

RFIPrefix = "RFI "
RFINum = Range("E4") & " - "
JobNum = Range("B4") & " - "
JobName = Range("B5")
Exten = ".pdf"
'
Range("A1:E28").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ChDir "C:\Users" & Environ("Username") & "\Downloads"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users" & Environ("Username") & "\Downloads" & RFIPrefix & RFINum & JobNum & JobName & Exten _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=True, OpenAfterPublish:=False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0)
.BottomMargin = Application.InchesToPoints(0)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.CenterHorizontally = True
.CenterVertically = False
.PaperSize = xlPaperA4
.BlackAndWhite = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
Application.PrintCommunication = True

End Sub