loop through Rows in Range and check value in a column

[Qroozn]

Hey guys. I haven't done VBA for years - but am trying to help out a colleague.

We currently have a sheet with approx. 50 pages. Different businesses want to see different pages printing.
Today.. someone goes through and manually works out which pages to print.


I have the below sample in Sheet1 Range A1:D5.
Lets assume I am running VBA for Business B.
I would like to
1) loop through each row in range and identify if Column "C" (businessB) has a value of "YES".
2) if the value is "YES" then I want to Set the TAB NAME (Column A) to print

Alternatively I might run the macro for BusinessA or C so am looking for appropriate column referencing.
Can someone please help out with VBA code? Thanks

Print Page BusinessA BusinessB BusinessC
Page 1 Yes Yes
page 2 Yes
page 3 Yes
page 4 Yes

 

[ttray33y]

I was confused with "2) if the value is "YES" then I want to Set the TAB NAME (Column A) to print"

try this though:

Private Sub testZ()


Dim lsheet1 As Worksheet
Dim lrow1 As Long
Dim BizVal As Variant
Dim x As Long
Dim i As Long
Dim OsTR As String
Dim NsTR As String


Set lsheet1 = ActiveWorkbook.ActiveSheet


Application.ScreenUpdating = False


BizVal = "Business" & InputBox("Enter A, B or C")


Select Case BizVal
   Case "BusinessA"
      i = 2
   Case "BusinessB"
      i = 3
   Case "BusinessC"
      i = 4
End Select


lrow1 = lsheet1.Cells(Rows.Count, i).End(xlUp).Row


For x = 2 To lrow1
        If lsheet1.Cells(x, i) = "Yes" Then
        
        OsTR = lsheet1.Cells(x, 1)
        NsTR = Right(OsTR, Len(OsTR) - 5)
        With lsheet1
                ActiveWorkbook.Sheets("Sheet1").PrintOut From:=NsTR, To:=NsTR
        End With


        End If
Next x


Application.ScreenUpdating = True


End Sub