Goto sheet based on cell contents macro

[dgl_thrawn]

I have a workbook with various named sheets (eg AM38-03-037-A) and a contents sheet with a list of these sheets exactly as they appear on the sheet name tab. As these sheets have issue numbers and need to be up-issued occasionally, I'd like to be able to have a macro run for each item that will look up the sheet name in the relevant cell and go to that sheet. At the moment I have to type the sheet name into the macro. I currently have:

Sub Button38_Click()
'
' Button38_Click Macro
' Macro recorded 19/04/01 by David Lawrence
'

'
Sheets("AM38-03-037-A").Select
End Sub

If sheet AM38-03-037-A is up-issued to AM38-03-037-B, I have to manually change it. If that change is already typed into a cell (say A37) on the contents sheet, can I refernce the contents of the cell instead?

I am using Excel 97.

dgl_thrawn

 

[LEXCERM]

Do you mean something like this:-

Sub Macro1()

On Error GoTo not_found

Dim change As String
change = Sheets("CONTENTS").Range("a37").Value

Sheets(change).Select
Exit Sub

not_found:
MsgBox "Sheet not found.", vbCritical, "Error"

End Sub

Regards,
PAUL.

 

[dgl_thrawn]

Brilliant! That worked exactly as I wanted. Thank you.

dgl_thrawn

 

[dgl_thrawn]

I now have a follow-up question.

If the macro button is on cell D37 and I'm looking at the contents of cell A37, is there a way of using the offset property to be able to do the same reference.

I want to be able to copy the contents of the macro to the button on cell D38 and have it automatically reference cell A38.

I assume I'd need to put the 'offset' property in the line:

change = Sheets("CONTENTS").Range("A37").Value

but I don't know where.

dgl_thrawn