Filter Multiple Sheets and copy results to another

I have a very limited knowledge of VBA coding and have thus hit my wall... I need to be able to copy rows that meet a certain criteria from multiple sheets into a report sheet. I have a code that I have modified that achieves this, but only on one sheet at a time and only if the button that runs the macro is in the sheet with the data. I want to be able to put the button on a summary sheet at the front and have all the results pulled through to a second sheet next to it named "Report". Whenever I try to get it to refer to multiple sheets I get a compile error, can anyone help? I don't really want to use a pivot table as I want to keep the same formatting (all the sheets are laid out identically but with different values in the data set).

Here's the code I have so far:


'In a userform

Option Explicit

Private Sub CommandButton1_Click()
Dim rng As Range

'Clear Contents to show just new search data
Worksheets("Report").Range("A5:R100").ClearContents
'Set Error Handling
On Error GoTo ws_exit:
Application.EnableEvents = False
'Set Range
Set rng = ActiveSheet.UsedRange
'Cancel if no value entered in textbox
If TextBox1.Value = "" Then GoTo ws_exit:
'Call function Filterandcopy
FilterAndCopy rng, TextBox1.Value
rng.AutoFilter
'Exit sub
ws_exit:
Set rng = Nothing
Application.EnableEvents = True
Unload Me
End Sub

Private Sub CommandButton2_Click()
'Cancel Button
Unload Me
End Sub

'In a Standard Module

Option Explicit
Function FilterAndCopy(rng As Range, Choice As String)

Dim FiltRng As Range
'Set the column to filter (In This Case 1 or A)
'Change as required
rng.AutoFilter Field:=5, Criteria1:=Choice
On Error Resume Next
Set FiltRng = rng.SpecialCells(xlCellTypeVisible).EntireRow
On Error GoTo 0

'Copy Data across to Report
FiltRng.Copy Worksheets("Report").Range("A5")
'Display Data
Worksheets("Report").Select
Range("A5").Select
Set FiltRng = Nothing
End Function

Sub formshow()
'Show Search Form
UserForm1.Show
End Sub

Hope this helps

I took your start and changed to loop through all worksheets in workbook.
It ignores "Sheet1", but processes the rest

You want to look at commandbutton 2 code in the attached