Any ideas? It would be great of I could delete all but one of the hidden worksheets. It would be perfect if I could tell the macro not to delete one of the hidden worksheets (called "Test O2 and CO2"), but if that is too difficult, then just one to delete all the hidden sheets would be fine.

Any help would be much appreciated on this one,

James

Hi,

I would like to delete certain ranges across mutliple worksheets, for example range F7:F17, P7:P17, Q7:17, across mutilple worksheets whether they are hidden sheets or not hidden, what I would also like is a message box that asks for confirmation of whether they wish to proceed to delete (would it be possible to include the range in the message box, what is about to be deleted - then a confirmation message saying delete has been actioned or cancelled - is it also possible to put a password on the before running the macro if the user selects yes to delete.

Many thanks

I have working code to unhide specific worksheets from a large number of hidden worksheets and would like to be able to delete all the hidden sheets when the workbook is saved or maybe have a button to delete all the hidden (un-needed) worksheets beside the selection drop-down menu, so that only the selected sheets are saved.

Geoff

I have a workbook with one worksheet which appears to be empty, however the
saved file is 3.5Mb. I am at a loss as to where all that content is?

Hi there,

I have built a monster spreadsheet, with the aim for it to cover all possible scenarios at my job. It basically involves a sheet for each type of test I may do. Some days it may need 1 sheet, others it may need 10. These all get populated into a single "Report" sheet.

What I am pulling my hair out over is trying to create 2 macros, which would be run one after the other.

The first would be to delete all hidden sheets in the workbook.

The second would be to delete all hidden rows, in the "Report" sheet only, and at the same time find any ",!#REF!" occurences that may have been generated by deleting rows, and replacing them with nothing. i.e. some cells may average 10 cells, deleting some rows will introduce say 5 valid cells, and 5 which have been deleted, leaving the ",!#REF!".

Can anyone out there help me out with these 2 macros please?

Thanks, James

Hi everyone,

My first post. I've been using this site for years and it is awesome.

Here is the situation. We have a workbook with a start page. Users answer questions on this sheet and based on the answer, various worksheets can be either made visible or hidden. Everything is working fine with this. Feedback from our end users is that they also want the ability to show/hide worksheets regardless of how questions are answered on the start page - like an override function.

I have started work on a new tab with a list of all the applicable worksheets within the workbook down column A with a dropdown in column B with the option to show/hide. This code is triggered by the "Private Sub Worksheet_Change(ByVal Target As Range)" line. The end user can choose which tabs to show/hide based on the situation. The problem is that column B is currently blank, so I was searching for code that can assist with populating the visible state of tabs based on column A.

I was going to continue down this path, but came across this nice piece of code that may work and looks cooler, but I do not understand it enough to modify. Now this code lists all sheets and prints out the ones you checkmark. What I would like is it to list all visible/hidden (but NOT very hidden) sheets and already have the ones visible checked, then the user can check or remove check from sheets to show/hide.

http://www.ozgrid.com/forum/showthread.php?t=19572

FromWill Riley

VB:

 
Sub SelectSheets() 
    Dim i As Integer 
    Dim TopPos As Integer 
    Dim SheetCount As Integer 
    Dim PrintDlg As DialogSheet 
    Dim CurrentSheet As Worksheet 
    Dim cb As CheckBox 
    Application.ScreenUpdating = False 
     
     ' Check For protected workbook
    If ActiveWorkbook.ProtectStructure Then 
        MsgBox "Workbook is protected.", vbCritical 
        Exit Sub 
    End If 
     
     ' Add a temporary dialog sheet
    Set CurrentSheet = ActiveSheet 
    Set PrintDlg = ActiveWorkbook.DialogSheets.Add 
     
    SheetCount = 0 
     
     ' Add the checkboxes
    TopPos = 40 
    For i = 1 To ActiveWorkbook.Worksheets.Count 
        Set CurrentSheet = ActiveWorkbook.Worksheets(i) 
         ' Skip empty sheets And hidden sheets
        If Application.CountA(CurrentSheet.Cells) <> 0 Then 
            SheetCount = SheetCount + 1 
            PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5 
            PrintDlg.CheckBoxes(SheetCount).Text = _ 
            CurrentSheet.Name 
            TopPos = TopPos + 13 
        End If 
    Next i 
     
     ' Move the OK And Cancel buttons
    PrintDlg.Buttons.Left = 240 
     
     ' Set dialog height, width, And caption
    With PrintDlg.DialogFrame 
        .Height = Application.Max _ 
        (68, PrintDlg.DialogFrame.Top + TopPos - 34) 
        .Width = 230 
        .Caption = "Select sheets to print" 
    End With 
     
     ' Change tab order of OK And Cancel buttons
     ' so the 1st option button will have the focus
    PrintDlg.Buttons("Button 2").BringToFront 
    PrintDlg.Buttons("Button 3").BringToFront 
     
     ' Display the dialog box
    CurrentSheet.Activate 
    Application.ScreenUpdating = True 
    If SheetCount <> 0 Then 
        If PrintDlg.Show Then 
            For Each cb In PrintDlg.CheckBoxes 
                If cb.Value = xlOn Then 
                    If Not Worksheets(cb.Caption).Visible Then 
                        With Worksheets(cb.Caption) 
                            .Visible = True 
                            .Activate 
                            .PrintOut 
                            .Visible = False 
                        End With 
                    Else 
                        With Worksheets(cb.Caption) 
                            .Activate 
                            .PrintOut 
                        End With 
                    End If 
                     ' ActiveSheet.PrintPreview 'for debugging
                End If 
            Next cb 
        End If 
    Else 
        MsgBox "All worksheets are empty." 
    End If 
     
     ' Delete temporary dialog sheet (without a warning)
    Application.DisplayAlerts = False 
    PrintDlg.Delete 
     
     ' Reactivate original sheet
    CurrentSheet.Activate 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

Hi,

I have a file with VBA but don't want the my client to see some of my worksheets or don't want them to revise any of my stuff in this Excel file. I would like to either hide my working worksheets or protected the worksheets. However, I've tried both but the VBA shows as Debug. How can I hide the sheets or protected them and the VBA still works.

Thanks.

Hello

The sheets in my worksheet seem to have disappeared off the bottom of my worksheet so I can only access 1 sheet

Tried some things in advanced options but it seems to have ticked show all windows in the taskbar in display

Could it be something to do with moving files from one laptop to another with different XL versions?

Thanks
Nick

I am having an issue with a workbook on the open in excel 2007. I open the workbook and the sheets are not visible. I then click view, arrange all, tiled, and OK. The worksheet appears and I save it, however the next time I reopen the workbook, the sheet is hidden again.

Has anyone seen this before and/or have any ideas on how to fix this?

Thanks

Hi,

I have the following marco to delete all hidden sheets in a workbook.

When this is ran you are prompted to confirm you would like to delete each sheet individually. Can someone tell me how this can be done automatically without the prompt?

I have tried enableevents= false events but this doesn't work.

Thanks,

Ali





Code:

Sub deletehidden()

Dim a As Integer


a = 1

While a <= Worksheets.Count

If Not Worksheets(a).Visible Then

Worksheets(a).Delete
Else
a = a + 1
End If
Wend

End Sub