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]

When you copy a worksheet within your workbook, Excel will change the table name to avoid any confusion. Click on a cell in the table of the copied sheet, then click the Table Tools design tab on the ribbon and look for the table name on the left end of the ribbon. Change the name in your macro (Table1) wherever it appears to the appropriate table name for the table on your copied sheet.

 

[dmjexcel]

Hmm ok. That would still require me to create an additional macro for each of the new workbooks. I guess what I'm asking is there a way for one macro to perform the hide function (and another singular macro to perform the unhide function) across multiple worksheets in the same workbook?

 

[JoeMo]

Hmm ok. That would still require me to create an additional macro for each of the new workbooks. I guess what I'm asking is there a way for one macro to perform the hide function (and another singular macro to perform the unhide function) across multiple worksheets in the same workbook?

Not clear to me what you want. You want a macro that loops through every sheet in the workbook? Is there only a single table on each sheet?

 

[dmjexcel]

Yes there is one single table on each sheet. Same type of table on each sheet that I would want the same macro to apply to.

 

[My Aswer Is This]

Are you asking about new macro for each "Worksheet" or each "Workbook"

Because you just said:

require me to create an additional macro for each of the new workbooks

 

[My Aswer Is This]

If you have Only one Table on each sheet.
And Sheet(1) Table name is "Table1"
And sheet (2) Table name is "Table2"
And so on try this script.

Sub Select_Table()
ActiveSheet.ListObjects("Table" & ActiveSheet.Index).Range.AutoFilter Field:=6, Criteria1:= _
 "<>0", Operator:=xlAnd
End Sub

 

[JoeMo]

Yes there is one single table on each sheet. Same type of table on each sheet that I would want the same macro to apply to.

This is untested. If it does what you want, you can easily adapt it to create the Unhide macro.

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

 

[dmjexcel]

My Aswer Is This,

That worked perfectly! Thank you so much!

 

[dmjexcel]

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.