You are getting the error, because there is no data being filtered. In other
words, although AutoFilter Mode is on all rows are visible. I don't normally
use the On Error Resume Next, but this case it may make sense to use it.
By the way, you don't have to select a sheet to run your code. Just
reference your sheet by name. Put the sheet name in where I have Your Sheet
Name Here or just use the code below (which is yours).
Hope this helps! If so, let me know, click "YES" below.
Private Sub CommandButton1_Click() 'Clears all filters
On Error Resume Next
Sheets("Your Sheet Name Here").ShowAllData
On Error GoTo 0
End Sub
Private Sub CommandButton1_Click() 'Clears all filters
Range("A3").Select
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0
End Sub