Create and print report from across multiple worksheets based on cell values

I have created a Macros enabled workbook with multiple worksheets they all have the same column structure but on certain pages some columns are hidin. what i am trying to do is on my report sheet i am wanting to select a Field supervisor and be able to create a report for that field supervisor and print it off showing all jobs and the status of each job that is assigned to them.

See attached file, I hope that it's what you need.

This is the code that I used:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Cells.Count = 1 Then
      If Target.Address(0, 0) = "B8" Then
         Call refreshReport
      End If
   End If
End Sub
Private Sub refreshReport()
   Dim myRange As Range, mySh As Worksheet
   Dim lastRow As Long, destRow As Long, r As Long
   Dim visibleRows As Long, c As Integer
   
   Application.EnableEvents = False
   Application.ScreenUpdating = False
   
   destRow = 10
   With ThisWorkbook.Sheets("reports")
      .Range("11:" & Rows.Count).ClearContents
      For Each mySh In ThisWorkbook.Sheets
         If LCase(mySh.Name) <> "main" _
            And LCase(mySh.Name) <> "reports" _
            And LCase(mySh.Name) <> "info" Then
            mySh.AutoFilterMode = False
            lastRow = mySh.Cells(1, 5).CurrentRegion.Rows.Count
   
            For r = 6 To lastRow
               If mySh.Cells(r, 1) = .Range("b8") Then
                  destRow = destRow + 1
                  .Range("a" & destRow & ":l" & destRow).Value = mySh.Range("b" & r & ":m" & r).Value
               End If
            Next r
            If destRow > 11 Then
               Range("A11:N11").Copy
               Range("A12:N" & destRow).PasteSpecial Paste:=xlPasteFormats
               Application.CutCopyMode = False
               .Range("a11").Activate
            End If
         End If
      Next mySh
   End With
   Application.EnableEvents = True
   Application.ScreenUpdating = True
End Sub

Regards,
Antonio

Try with this new code. You don't find 'Mike Oliver ' because in Info sheet there is a blank at the end of the name, and you don't find 'HARRY BURKHART' because in Info sheet it's writtend without 'D' and in the sheets is written 'HARRY BURKHARDT'.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Cells.Count = 1 Then
      If Target.Address(0, 0) = "B8" Then
         Call refreshReport
      End If
   End If
End Sub
Private Sub refreshReport()
   Dim myRange As Range, mySh As Worksheet
   Dim lastRow As Long, destRow As Long, r As Long
   Dim visibleRows As Long, c As Integer
   
   Application.EnableEvents = False
   Application.ScreenUpdating = False
   
   destRow = 10
   With ThisWorkbook.Sheets("reports")
      .Range("11:" & Rows.Count).ClearContents
      For Each mySh In ThisWorkbook.Sheets
         If InStr("ready,scheduled,inprogress,onhold,ctl,complete", LCase(mySh.Name)) > 0 Then
            mySh.AutoFilterMode = False
            lastRow = mySh.Cells(1, 5).CurrentRegion.Rows.Count
   
            For r = 6 To lastRow
               If mySh.Cells(r, 1) = .Range("b8") Then
                  destRow = destRow + 1
                  .Range("a" & destRow & ":l" & destRow).Value = mySh.Range("b" & r & ":m" & r).Value
               End If
            Next r
            If destRow > 11 Then
               Range("A11:N11").Copy
               Range("A12:N" & destRow).PasteSpecial Paste:=xlPasteFormats
               Application.CutCopyMode = False
               .Range("a11").Activate
            End If
         End If
      Next mySh
   End With
   Application.EnableEvents = True
   Application.ScreenUpdating = True
End Sub

Regards,
Antonio

Antonio,
Thanks for you help this is just what I was looking for

now i got to figure out how to convert the workbook it so i can use it on Google sheets

Antonio,
this works great but i have one more piece of the puzzle i need help with. on my report sheet i would also like to print out reports for who ever the jobs are issued to as well so i can filter either by field supervisor or by the issued to column. can you help with that ?

OK someting has went array with my VBA Code it is not pulling every column from pages in the report what have i done lol