Excel VBA Code Help

Question

Hello, I'm new to VBA....

Aside from recording my own macros I'm not confident writing my own code from scratch. I have a Macro where I hide and unhide information, charts, drop downs, check boxes etc. on one sheet but I need to copy the same sheet 19 more times and know there's a way to code the Macros once and work for all sheets individually. Could anyone help me!! Below is a two of my macros I need to duplicate on all other sheets. Any help would be greatly appreciated!!!

Sub HIDEINJECTION()

'

' HIDEINJECTION Macro

' Hides only Injection Information

'

' Keyboard Shortcut: Ctrl+i

'

    Rows("41:189").Select

    Selection.EntireRow.Hidden = True

    ActiveSheet.Shapes.Range(Array("Check Box 444")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Check Box 438")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Check Box 212")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Check Box 209")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Check Box 201")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Check Box 198")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Drop Down 197")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Drop Down 160")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Drop Down 141")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Drop Down 139")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Drop Down 137")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Drop Down 136")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Drop Down 92")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Group Box 55")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Drop Down 160")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Drop Down 160")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Drop Down 141")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Drop Down 141")).Select

    ActiveSheet.Shapes.Range(Array("Drop Down 141")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Drop Down 65")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Drop Down 65")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Drop Down 60")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Check Box 449")).Visible = msoFalse

    Application.CommandBars("Selection").Visible = False

    ActiveWindow.SmallScroll Down:=-20

    ActiveSheet.Shapes.Range(Array("Option Button 174")).Select

    Range("C3").Select

    ActiveWindow.SmallScroll Down:=-8

End Sub

Sub SHOWINJECTION()

'

' SHOWINJECTION Macro

' Shows only injection info

'

'

    Rows("40:190").Select

    Selection.EntireRow.Hidden = False

    ActiveWindow.SmallScroll Down:=24

    ActiveSheet.Shapes.Range(Array("Check Box 444")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Check Box 438")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Check Box 212")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Check Box 209")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Check Box 449")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Check Box 201")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Check Box 198")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Drop Down 197")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Drop Down 160")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Drop Down 141")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Drop Down 139")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Drop Down 137")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Drop Down 136")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Drop Down 92")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Drop Down 60")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Group Box 55")).Visible = msoTrue

    Application.CommandBars("Selection").Visible = False

    Range("B41").Select

    ActiveWindow.SmallScroll Down:=4

    ActiveWindow.Zoom = 85

End Sub

Answer 1

Here is a working sample, same code as I used above, just the objects are different.

https://www.dropbox.com/s/6n7ftr1rm0h1hb8/b6b3b...

Andreas.

Answer 2

Try substituting "oSheet" for "Sheet" in all statements..

If you use replace for this in the vbEditor, make sure that you do whole word only and do not make it specific for case.

Answer 3

Okay I'm obviously not understanding, is this what you meant?... Like I said I'm new to VBA and can understand what the code means but formatting it and what to input next is foreign to me. If you have any websites or videos that would help me, maybe its just best if you could include their links. I don't want to waist your time with the same question over and over again.

Sub HIDEINJECTION()

'   Hide_Show_Ijection ActiveSheet, True

' HIDEINJECTION Macro

' Hides only Injection Information

' Keyboard Shortcut: Ctrl+i

'Dim ws as Worksheet

For Each Ws In Worksheets

HIDEINJECTION

  With Ws

    Rows("41:189").EntireRow.Hidden = Not HideState

    With .Shapes

      .Item("Check Box 444").Visible = Not HideState

      .Item("Check Box 438").Visible = Not HideState

      .Item("Check Box 212").Visible = Not HideState

      .Item("Check Box 209").Visible = Not HideState

      .Item("Check Box 201").Visible = Not HideState

      .Item("Check Box 198").Visible = Not HideState

      .Item("Drop Down 197").Visible = Not HideState

      .Item("Drop Down 160").Visible = Not HideState

      .Item("Drop Down 141").Visible = Not HideState

      .Item("Drop Down 139").Visible = Not HideState

      .Item("Drop Down 137").Visible = Not HideState

      .Item("Drop Down 136").Visible = Not HideState

      .Item("Drop Down 92").Visible = Not HideState

      .Item("Group Box 55").Visible = Not HideState

      .Item("Drop Down 160").Visible = Not HideState

      .Item("Drop Down 160").Visible = Not HideState

      .Item("Drop Down 141").Visible = Not HideState

      .Item("Drop Down 141").Visible = Not HideState

      .Item("Drop Down 65").Visible = Not HideState

      .Item("Drop Down 65").Visible = Not HideState

      .Item("Drop Down 60").Visible = Not HideState

      .Item("Check Box 449").Visible = Not HideState

   End With

  End With

End Sub