reference to another sheet not in wkbk brings up dialog box

  • Thread starter Thread starter Valerie
  • Start date Start date
V

Valerie

I have a macro that writes a formula referencing another sheet in the
workbook. When this sheet isn't in the workbook, Excel opens the dialog box
to find the sheet and repeats for every line that contains the reference. I
would like to add something around the section referring to another sheet to
bypass that section if the sheet is not in the workbook. Is this possible?
 
Valeire

Dim ShtName As String
'Other Code

On Error Goto NoSheet:
ShtName = Worksheets("Sheet that may not be there").Name
'Code for formula referencing
NoSheet:

'Finish up code

HTH,
Bernie
MS Excel MVP
 
Perfect!! Thanks so much!

Bernie Deitrick said:
Valeire

Dim ShtName As String
'Other Code

On Error Goto NoSheet:
ShtName = Worksheets("Sheet that may not be there").Name
'Code for formula referencing
NoSheet:

'Finish up code

HTH,
Bernie
MS Excel MVP
Click to expand...
 
Back
Top