VBA code for button on one sheet to filter table in another sheet

[t_mo31]

Hello,

I have two tables on two sheets in excel, the first asks the user to score a number of criteria in cells D5 to D107. The next sheet is a copy of this table (with the values of D5:D107 being copied from Sheet 1 to Sheet 2), but then has additional columns where the user needs to add more information.

I inserted a command button and drafted a macro (found online) so that the table on Sheet 2 auto filters so only criteria scored as less than 3 in Sheet 1 are shown in Sheet 2.

However, I want the command button to be on Sheet 1, but alter the table on Sheet 2. I dont know how to edit the macro so that it can do this.

Any help greatly appreciated!

See macro:

Private Sub CommandButton1_Click()
Dim range_to_filter As Range
Set range_to_filter = Range("D5:D107")

range_to_filter.AutoFilter Field:=1, Criteria1:="<2"

End Sub

 

[mrshl9898]

Try adding the sheet name (called it sSheet2 below)

Private Sub CommandButton1_Click()
Dim range_to_filter As Range
Set range_to_filter = Sheets("Sheet2").Range("D5:D107")

range_to_filter.AutoFilter Field:=1, Criteria1:="<2"

End Sub

 

[t_mo31]

Hi,

I get "runtime error 9: subscript out of range, and when I debug it highlights the row of code with the added "sheets" command.

See attached (and note for ease in my post i just said sheet 1 sheet 2, but they do actually have names hence the slight difference in code)


Thanks!

 

[mrshl9898]

Check for spaces in the Sheet name or try renaming it. A runtime error 9 usually indicates you're referring to a sheet that doesn't exist.

 

[t_mo31]

Oh yes, that was it, thanks so much!