Unhide Sheet2 and its rows from a button on Sheet1

    I have a spreadsheet where I want to hide empty rows and sheets if they are not going to be used. Because I have a few sheets that I want to have these macros on, I have put all the buttons on the first sheet.


    I want a macro that will unhide all of the rows, but on the same button I also want it to unhide the sheet if it is hidden. What I have at the minute is:


    Code
    Sub Unhide()
' Macro4 Macro
    Sheets("Sheet1").Select
    Sheets("Sheet2").Visible = True
    Rows("15:316").Select
    Selection.EntireRow.Hidden = False
    Range("B1").Select
    Sheets("Sheet1").Select
    Range("A1").Select
End Sub


    Sheet1 is where I want all the buttons
    Sheet2 is the sheet that may be hidden and may have hidden rows


    This macro unhides Sheet2 but selects and unhides rows 15:316 on Sheet1, which were never hidden.


    Any help would be great!

    Re: Unhide Sheet2 and its rows from a button on Sheet1


    You need to refer to the sheet in your code. Excel is assuming the rows are on the sheet from which the code is run, i.e. Sheet1.


    Code
    Sub Unhide() 
     ' Macro4 Macro
  With  Sheets("Sheet2")
    .Visible = xlsheetvisible
   . Rows("15:316").EntireRow.Hidden = False 
   End With
End Sub

    Re: Unhide Sheet2 and its rows from a button on Sheet1


    This comes back with a 'Sub Script out of range' error. Do you know what I am doing wrong?

    Re: Unhide Sheet2 and its rows from a button on Sheet1


    Ah! I need this to be the sheet number, and not what I have named it, as the sheets are going to be renames at points.

    Re: Unhide Sheet2 and its rows from a button on Sheet1


    One way to avoid that error would be to refer to the Sheet code name. This can only be changed in the VBA dev environment. The sheet tab name can then be changed without causing the code to break.


    Code
    Sub Unhide() 
     ' Macro4 Macro
     '// Sheet2 is the default name. This can only be changed using the Properties dialog in the VBA editor
    With  Sheet2 
        .Visible = xlsheetvisible 
        .Rows("15:316").EntireRow.Hidden = False 
    End With 
End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login