Duplicating a VBA code on different Sheets in same

Question

I am trying to run the same code throughout all the sheets on the given workbook. I have first Worksheet in workbook assigned to a VBA Code and formatted with shapes/buttons and have made two identical copies of that sheet. However, when the copy was made my VBA code will not work on the copies it only works on the first and original sheet. Is there any way to loop the code to run on the buttons/shapes. I don't need the table data transferred it should stay within each sheet. The VBA code is only to the table headers and subtabs. Below is the code I have.

    Sub TabCase()
    With Sheet1
    Application.ScreenUpdating = False
    .Shapes("CaseOn").Visible = msoCTrue
    .Shapes("CaseOff").Visible = msoFalse

    .Shapes("DemOn").Visible = msoFalse
    .Shapes("DemOff").Visible = msoCTrue

    .Shapes("RefOn").Visible = msoFalse
    .Shapes("RefOff").Visible = msoCTrue

    .Shapes("SDOHOn").Visible = msoFalse
    .Shapes("SDOHOff").Visible = msoCTrue

    .Range("B:K").EntireColumn.Hidden = False
    .Range("M:AO").EntireColumn.Hidden = True
     Application.ScreenUpdating = True
     End With
     End Sub
     Sub TabDem()
     With Sheet1
     Application.ScreenUpdating = False
    .Shapes("CaseOn").Visible = msoFalse
    .Shapes("CaseOff").Visible = msoCTrue

    .Shapes("DemOn").Visible = msoCTrue
    .Shapes("DemOff").Visible = msoFalse

    .Shapes("RefOn").Visible = msoFalse
    .Shapes("RefOff").Visible = msoCTrue

    .Shapes("SDOHOn").Visible = msoFalse
    .Shapes("SDOHOff").Visible = msoCTrue

    .Range("M:Y").EntireColumn.Hidden = False
    .Range("B:K,AA:AO").EntireColumn.Hidden = True
     Application.ScreenUpdating = True

    End With
    End Sub
    Sub TabRef()
    With Sheet1
    Application.ScreenUpdating = False
    .Shapes("CaseOn").Visible = msoFalse
    .Shapes("CaseOff").Visible = msoCTrue

    .Shapes("DemOn").Visible = msoFalse
    .Shapes("DemOff").Visible = msoCTrue

    .Shapes("RefOn").Visible = msoCTrue
    .Shapes("RefOff").Visible = msoFalse

    .Shapes("SDOHOn").Visible = msoFalse
    .Shapes("SDOHOff").Visible = msoCTrue

    .Range("AA:AE").EntireColumn.Hidden = False
    .Range("B:Z,AF:AO").EntireColumn.Hidden = True
     Application.ScreenUpdating = True

    End With
    End Sub
    Sub TabSDOH()
    With Sheet1
    Application.ScreenUpdating = False
    .Shapes("CaseOn").Visible = msoFalse
    .Shapes("CaseOff").Visible = msoCTrue

   .Shapes("DemOn").Visible = msoFalse
   .Shapes("DemOff").Visible = msoCTrue

  .Shapes("RefOn").Visible = msoFalse
  .Shapes("RefOff").Visible = msoCTrue

  .Shapes("SDOHOn").Visible = msoCTrue
  .Shapes("SDOHOff").Visible = msoFalse

  .Range("AG:AO").EntireColumn.Hidden = False
  .Range("B:AF").EntireColumn.Hidden = True
   Application.ScreenUpdating = True
   End With
   End Sub

   Sub loopAcrossSheets()
   temp = Array("County", "City", "CSV")
    For Each SheetName In temp
   
    Next
    End Sub

Copy everything from With ... to End With in each Sub and paste twice more (right after each End With. Then change the sheet name from sheet1 to Sheet2 or whatever for each block. There's better ways, but that's the non-complicated don't-know-what-I'm doing way that is probably a good first pass. — JNevill
– JNevill, Commented Jan 28, 2022 at 22:41

Tim Williams · Accepted Answer · 2022-01-28 23:09:53Z

1

Rework each method to take a parameter of type Worksheet - eg:

Sub TabCase(ws As Worksheet)
    Application.ScreenUpdating = False
    With ws
        .Shapes("CaseOn").Visible = msoCTrue
        .Shapes("CaseOff").Visible = msoFalse
        .Shapes("DemOn").Visible = msoFalse
        .Shapes("DemOff").Visible = msoCTrue
        .Shapes("RefOn").Visible = msoFalse
        .Shapes("RefOff").Visible = msoCTrue
        .Shapes("SDOHOn").Visible = msoFalse
        .Shapes("SDOHOff").Visible = msoCTrue
        .Range("B:K").EntireColumn.Hidden = False
        .Range("M:AO").EntireColumn.Hidden = True
     End With
     Application.ScreenUpdating = True
End Sub

Then instead of calling it like:

TabCase

you'd call it and pass in a worksheet as an argument:

TabCase Sheet1 'or Sheet2, or ThisWorkbook.Sheets("NewSheet") etc

answered Jan 28, 2022 at 23:09

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Duplicating a VBA code on different Sheets in same

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related