Please help...

I have been looking for a solution to this problem for a
couple of years now.. but can't seem to get around it..

I have a workbook ([Job Analysis 2004.xls]) with macro
that I run that:

1. Copies a mastersheet within the workbook to a new
sheet.

2. Sets new name of the sheet, let say "04-500"

3. Using the variable LstYrMat"='[Job Analysis 2003.xls]"
& JobNo & "'!$B$23, I create a formula in a cell to link
to sheet "04-500" in last years workbook ([Job Analysis )
2003.xls]"

My problem is that if the sheet did not exist in last
years workbook, when I set the formula in the cell, I get
the "Select Sheet" dialog box. I can't seem to find a way
to dismiss the dialog box programmatically. The user has
to sit there and hit cancel several times.

Any help I can get would be greatly, greatly appreciated.

Jeannie Flores

I don't believe there is a way to dismiss the dialog via code. Why not have
your macro open up the target workbook and see if the worksheet is there
before you write a formula referencing it? If you are concerned about
screen activity, you can use

application.screenupdating = false

to hide screen activity, and set back to true to show activity.

To see if a sheet exists once you open the workbook, do something like:

dim oS as worksheet
on error resume next
set oS = worksheets("somesheetname")
on errror goto 0
if os is nothing then
'code to handle no sheet exists
end if

To close the target workbook and not save, use

workbooks("Job Analysis 2003.xls").close false

Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"Jeannie Flores" wrote in message
...
Please help...

I have been looking for a solution to this problem for a
couple of years now.. but can't seem to get around it..

I have a workbook ([Job Analysis 2004.xls]) with macro
that I run that:

1. Copies a mastersheet within the workbook to a new
sheet.

2. Sets new name of the sheet, let say "04-500"

3. Using the variable LstYrMat"='[Job Analysis 2003.xls]"
& JobNo & "'!$B$23, I create a formula in a cell to link
to sheet "04-500" in last years workbook ([Job Analysis )
2003.xls]"

My problem is that if the sheet did not exist in last
years workbook, when I set the formula in the cell, I get
the "Select Sheet" dialog box. I can't seem to find a way
to dismiss the dialog box programmatically. The user has
to sit there and hit cancel several times.

Any help I can get would be greatly, greatly appreciated.

Jeannie Flores