CODEWITHSUNDEEP
excelvba
Jessica
Jessica

Reputation: 11

Duplicating a VBA code on different Sheets in same

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

Upvotes: 1

Views: 274

Answers (1)

Tim Williams
Tim Williams

Reputation: 166306

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

Upvotes: 1

Related Questions