unhide sheets macro

[Rahiem]

I have an excel file that has like 70 sheets that I need to unhide and it is a pain selecting each sheet one by one. I need help creating a macro that can first unhide all of the sheets in the file and format each sheet in print preview mode from columns A1 to I xl.End of data (to the last cell of data), this is so I can print my invoices right after I unhide them. Please paste the code in thread and thanks so much for your help.

 

[shyrath]

Hi

I always use this code to hide / unhide.

3 macro´s below. the top one I assign to a logo or image, so when clicked it will ask me if i want to hide or unhide

As for the print format. No experience here..

Sub HideOrUnhide()

Answer = MsgBox(Prompt:="Choose: Yes  to Hide sheets - No to Unhide sheets", _
Title:="Hide Sheets?", _
Buttons:=vbYesNoCancel + vbInformation + vbDefaultButton3)
   
'if yes then run this macro again
'If anything other than yes, then stop the macro
   
   If Answer = vbYes Then
   Call HideAllSheets
   Else
   UnHideAllSheets
   Exit Sub
   End If




End Sub



Sub HideAllSheets()
Dim sh As Worksheet
Dim sh2 As Worksheet

Set sh2 = ActiveSheet
Application.ScreenUpdating = False

For Each sh In ActiveWorkbook.Sheets
     If Not sh2.Name = sh.Name Then
          sh.Visible = xlSheetHidden
     End If
Next
Application.ScreenUpdating = True

End Sub

Sub UnHideAllSheets()
Dim sh As Worksheet
Dim sh2 As Worksheet

Set sh2 = ActiveSheet
Application.ScreenUpdating = False

For Each sh In ActiveWorkbook.Sheets
     If Not sh2.Name = sh.Name Then
          sh.Visible = xlSheetVisible
     End If
Next
Application.ScreenUpdating = True

End Sub

 

[texasalynn]

this will loop through and unhide the sheets

Sub Unhide()
    Dim wst As Worksheet
 
    For Each wst In Worksheets
        If Worksheets(wst).Hidden = True Then
                Worksheets(wst).Hidden = False
        End If
    Next wst
End Sub

HTH

 

[GTO]

I have an excel file that has like 70 sheets that I need to unhide and it is a pain selecting each sheet one by one. I need help creating a macro that can first unhide all of the sheets in the file and format each sheet in print preview mode from columns A1 to I xl.End of data (to the last cell of data), this is so I can print my invoices right after I unhide them. Please paste the code in thread and thanks so much for your help.

Greetings Rahiem,

I do not think you actually want to use print preview for each sheet, but rather, just want to update the print area of each sheet.

The below example presumes you keep the hidden sheets very hidden. This should display all the invoice sheets, print ea one, and rehide them.

If you are wanting to just print some of them, you can move a few lines of code to another Sub.

Hope this helps,

Mark

In a Standard Module:

Option Explicit
Dim wksHid()

Sub Sheets_Printout()
Dim _
wks         As Worksheet, _
i           As Integer, _
bolSaved    As Boolean
    
    
    ReDim wksHid(0)
    
    
    '// Set a flag to the workbook's current saved status.                  //
    bolSaved = ThisWorkbook.Saved
    
    
    '// Kill updating, eliminates screen "flicker" and speeds up processing //
    Application.ScreenUpdating = False
    
    
    '// This presumes that the hidden sheets are very xlSheetVeryHidden.    //
    '// Loop thru sheets, building an array of hidden sheets' names, while  //
    '// setting their print area and printing ea                            //
    For Each wks In ThisWorkbook.Worksheets
        With wks
            If .Visible = xlSheetVeryHidden Then
                wksHid(UBound(wksHid)) = .Name
                .Visible = xlSheetVisible
                .PageSetup.PrintArea = _
                    .Range("A1:I" & .Cells(Rows.Count, "I").End(xlUp).Row).Address
                
                
                '// Optional: if wanting to print ALL of the invoices*******//
                .PrintOut                                                  '//
                '//*********************************************************//
                
                ReDim Preserve wksHid(UBound(wksHid) + 1)
            End If
        End With
    Next
    
    
    '// Delete the last element of the array, as it will  be empty          //
    ReDim Preserve wksHid(UBound(wksHid) - 1)
    
    
    '// Optional:  If .Printout was used above to print all invoices, then  //
    '// we can loop thru our array of sheet names to re-hide the formerly   //
    '//hidden sheets.  If .PrintOut was not used, move this to a seperate   //
    '// Sub.                                                                //
    For i = LBound(wksHid()) To UBound(wksHid())                           '//
        ThisWorkbook.Worksheets(wksHid(i)).Visible = xlSheetVeryHidden     '//
    Next                                                                   '//
    '//*********************************************************************//
    
    
    '// turn updating back on                                               //
    Application.ScreenUpdating = True
    
    '// Use flag to ignore the unhiding/hiding sheets if workbook was in a  //
    '// saved = True status before                                          //
    ThisWorkbook.Saved = bolSaved
End Sub