Thread: Clearing autofilters and sorting on a non active sheet

Afternoon,

I have a table similar to this in a spreadsheet (it has a lot more locations and departments and the numbers are different but you get the gist)..

Loc1 Loc2 Loc3
Department 1 5 0 5
Department 2 6 156 66
Department 3 25 0 52
Department 4 0 0 1


I've created some dynamic ranges which chart the information based on user inputs but now I am getting the problem of pie charts leaving in zero values. Basically, I now want to be able to write three macros which filter the table (for each loc) to exclude any zero values. By recording some macros I have managed to do this when I have the sheet selected as the active sheet but as this is going to be for a dashboard I want it all to update automatically from the main page.

What I currently have is...

  Range("I65:K65").Select
    ActiveSheet.ShowAllData
    Range("I65").Select
    ActiveSheet.Range("$D$65:$M$83").AutoFilter Field:=6, Criteria1:="<>0", _
        Operator:=xlAnd

I am new to using macros, how do I change this so that it doesn't need to be on the active sheet?

Thanks,

Alice

with sheets("Sheet name")
.ShowAllData
    .Range("$D$65:$M$83").AutoFilter Field:=6, Criteria1:="<>0", _
        Operator:=xlAnd
End With