Using same macro in a copied worksheet

[dmjexcel]

Hello,

I am very very new to using macros. I currently have a macro set up (I used the record macro function, like I said, very new) to hide all values in a table that equal 0 and another macro to unhide the data. I would like to be able to copy this worksheet into the same workbook and have the macros work for that new worksheet. Is there a way to code the original macro to allow this? Currently I get an error on the copied worksheet and the only way I have been able to get around is to record the macro again in the new worksheet. This specific workbook could have up to 15 of the same tables in it, I would really like to avoid having to rerecord the macros that many times.

Here are the two current macros' codes:

Sub Hide()
'
' Hide Macro
'


'
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=6, Criteria1:= _
"<>0", Operator:=xlAnd
End Sub

Sub Unhide()
'
' Unhide Macro
'


'
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=6
End Sub

Thank you!

 

[JoeMo]

My Aswer Is This,

That worked perfectly! Thank you so much!

Does it work on a copied sheet? I ask because Excel doesn't always assign consecutive numbers to tables on copied sheets.

 

[JoeMo]

JoeMo I wasn't able to get yours to work. When I went into the debugger it said that Lob = nothing. I wasn't sure how to fix it.

My error, try this revision.

Sub Hide()
Dim sht As Worksheet, Lob As ListObject
For Each sht In Worksheets
    For Each Lob In sht.ListObjects
        Lob.Range.AutoFilter Field:=6, Criteria1:= _
        "<>0", Operator:=xlAnd
    Next Lob
Next sht
End Sub

 

[My Aswer Is This]

Glad too see you have what you want but I never saw in your original post where you said I want a script that will perform this same script on all my sheets at the same time.

 

[dmjexcel]

Yes sorry about that, still new to macros so I wasn't even sure how to phrase the question property.