August 11, 2010

Workbooks have a habit of growing over time as the reporting needs of an organization evolve. As this occurs it becomes increasingly important to limit the visible worksheets to those relevant for a particular reporting purpose or to those sheets most often used.

A logical seperation is between sheets that contain the data to be referenced (source sheets)  and the sheets that contain interactive formulae referencing the data on the source sheets. In the example below, I have used this code to hide all the sheets containing source data whenever a user opens the workbook.

Private Sub Workbook_Open()

Dim sht As Worksheet

For Each sht In ThisWorkbook.Sheets
 If sht.Name Like “*data” Then
  sht.Visible = False
 End If
Next sht

End Sub


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: