Try the following code placed in a standard module and assigned to a button:-
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets
If ws.Name <> "Master" Then
With ws.Range("Q1", ws.Range("Q" & ws.Rows.Count).End(xlUp))
.AutoFilter 1, "<>0"
Sheet1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
I'm assuming that you are using "<>0" (is not equal to zero) as the criteria to filter on. You can change this to suit yourself.
I'm also assuming that your Master sheet has the sheet code of Sheet1 (line 13 in the code above).
The code will filter Column Q of each sheet for the criteria ("<>0") and then transfer the relevant rows of data from each sheet to the Master sheet.
I hope that this helps.