Here’s the problem. You have a number of sheets that are named after Department codes. Those sheets contain the details for each department. You have reports throughout the model that refer to these department codes. You want to be able to select a cell that contains a department code and click a button that will take you to that department’s sheet.

That is when Excel’s VBA (macro) language comes to the rescue.

The VBA code to do that is incredibly simple and is shown below

Sub Open_Sheet_Name()
On Error Resume Next
 ActiveWorkbook.Sheets(CStr(ActiveCell.Value)).Activate
End Sub

The On Error Resume Next command handles the situation of selecting a cell that doesn’t contain a sheet name. In that case nothing will happen.

The CStr command command coverts all entries to strings (text) so that any sheets named with numbers are handled correctly.

This macro will work for any names or codes you use as sheet names. There are rules for sheet names and some symbols are not allowed in sheet names. Always watch out for leading and trailing spaces in the cell or the sheet name as these may stop the macro working.

Check out this post to see how to run a macro from a button on the Quick Access Toolbar