Hiding Unused Worksheets

[squaredealb]

I have a workbook that I use to import a text file and parse the data. The text file can contain up to 12 data sets, each of which I display in a different worksheet. Currently, all 12 sheets are visible, even when my text file contains fewer data sets. I?d like to be able to enter the number of data sets into the opening worksheet, and then hide any un-needed worksheets. Is there any way to set the visible status of sheets based on this?

Thanks.

[malik641]

Hey squaredealb, welcome to VBAX! :hi:

Are the sheets that have no data sets empty? You could use the following if that's the case (without inputting anything on a worksheet):
Public Sub HideEmptySheets()
' Hides worksheets that contain no data
Dim Sh As Excel.Worksheet

' Use On Error Resume Next because you cannot hide all worksheets
' in a workbook. So if you end up having an empty workbook this
' code will try to hide all worksheets. When it gets to the last
' sheet an Error will rise and be skipped over by the following statement.
On Error Resume Next
For Each Sh In ThisWorkbook.Worksheets
' Check if the last used cell is A1 and if it is indeed empty
' if both conditions are true, hide the sheet
If Sh.Cells.SpecialCells(xlCellTypeLastCell).Address = "$A$1" _
And Sh.Cells.SpecialCells(xlCellTypeLastCell).Value = "" Then _
Sh.Visible = xlSheetHidden
Next
End Sub

And to show all sheets:
Public Sub ShowAllSheets()
' Shows all worksheets in the current workbook
Dim Sh As Excel.Worksheet

' Loop through all sheets and unhide them.
' If the sheet is already visible, this will
' just set the sheet's visibility to the same
' setting as it was.
For Each Sh In ThisWorkbook.Worksheets
Sh.Visible = xlSheetVisible
Next
End Sub

And if you would rather Delete all unused sheets:
Public Sub DeleteEmptySheets()
' Deletes worksheets that contain no data
' First off, disable DisplayAlerts so a message
' box doesn't show up while the code is executing.
Application.DisplayAlerts = False
Dim Sh As Excel.Worksheet

' Use On Error Resume Next because you cannot delete all worksheets
' in a workbook. So if you end up having an empty workbook this
' code will try to delete all worksheets. When it gets to the last
' sheet an Error will rise and be skipped over by the following statement.
On Error Resume Next
For Each Sh In ThisWorkbook.Worksheets
' Check if the last used cell is A1 and if it is indeed empty
' if both conditions are true, delete the sheet
If Sh.Cells.SpecialCells(xlCellTypeLastCell).Address = "$A$1" _
And Sh.Cells.SpecialCells(xlCellTypeLastCell).Value = "" Then _
Sh.Delete
Next

Application.DisplayAlerts = True
End Sub

Hope this helps :)

[squaredealb]

Hi Joseph,
Thanks for the welcome and for the input. The sheets that have no data sets are not empty; they contain a set of functions to parse the imported data and to fit the data to a series of model equations. Currently, the workbook is designed to open to a data entry page where the user would enter background experimental information and browse for the data file to import. There are then import and parse sheets which are hidden to the user and 12 visible sheets named DataSet1 - DataSet 12. I'd like for the DataSet sheets to be hidden when the workbook is opened. The user would then enter the number of data sets into the data entry page and the appropriate number of sheets would be displayed. I tried your suggestion for hiding empty worksheets and received an error but this is probably due to the fact that they're not empty.
Thanks again,
Jeff

[mdmackillop]

Private Sub Workbook_Open()
For Each sh In Worksheets
If UCase(Left(sh.Name, 7)) = "DATASET" Then sh.Visible = False
Next
End Sub

[squaredealb]

Thanks mdmackillop. Should i use your lines instead of or in addition to the HideEmptySheets code that malik641 suggested? I tried both ways and it didn't seem to do anything. I'm still very new to VBA so please forgive me if I'm missing something simple.

[mdmackillop]

My code has to be pasted into the ThisWorkbook module. It will run when the workbook is opened.

[squaredealb]

Got it, thanks. It works to hide all of the DataSet sheets. To re-display the appropriate number of DataSet sheets, is there some way I can add a line to use cell B21 of sheet StartHere to specify the needed number of datasheets (i.e if B21 = 6, display DataSet1 through DataSet6)? Also, if I'm regurgitating a topic that's already been discussed here, any suggestions on previous posts would also be helpful. I dug around the forum looking for topics that may apply but didn't really find anything.