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.