The lines that turn off alerts are hiding the problem.

Application.DisplayAlerts = False
Workbooks.Open _
FileName:=ActiveSheet.Range("TemplateDir").Value & "Forecast.xls"
Application.DisplayAlerts = True

If you comment them out, you'll see that you're suppressing that "you have a
workbook by this name already open, reopening will lose your changes" message.

I'd dump that savecopyas and use a straight .SaveAs

Then you can still reopen the template file. I think I'd approach it like this:

strDirPath = ActiveSheet.Range("DirectoryPath").Value
strFileName = ActiveSheet.Range("Category").Value
strBC = ActiveSheet.Range("Entity").Value
strFullName = strDirPath & "\" & strFileName & "-" & strBC & ".xls"

ActiveWorkbook.SaveAs Filename:=strFullName, FileFormat:=xlWorkbookNormal

MsgBox "File has been copied to " & strFullName, vbOKOnly

Workbooks.Open _
Filename:=ActiveSheet.Range("TemplateDir").Value & "Forecast.xls"

'close the workbook with the code--the one you just "saved As"
Thisworkbook.Close savechanges:=false

It seems more straightforward to me.



taylorm wrote:

No, the filenames are different. The file being written out will have a
name such as FCST_Mar-2408.xls and it's being written to a completely
different directory where the template file name is Forecast.xls.
(I've used the word "template" rather loosely, it's actually an xls
file, not an xlt.)

Here's the code to the last part of the macro where we're Saving the
Copy As and then re-opening the original file. We've commented out the
UNC address and replaced it with the fully qualified drive mapped path
from a named range "TemplateDir", which we pickup by using the
cell("filename") function on the active sheet.

' Save file to destination directory, open original template
strDirPath = ActiveSheet.Range("DirectoryPath").Value
strFileName = ActiveSheet.Range("Category").Value
strBC = ActiveSheet.Range("Entity").Value
strFullName = strDirPath & "\" & strFileName & "-" & strBC &
".xls"
ActiveWorkbook.SaveCopyAs FileName:=strFullName
Response = MsgBox("File has been copied to " & strFullName,
vbOKOnly)
Application.DisplayAlerts = False
' Workbooks.Open
FileName:="\\FNFNSH42\VOL1\MCAPPL\Hypprod5.5\Exp\W orkbook\Process\2006\Forecast\Forecast.xls"
Workbooks.Open FileName:=ActiveSheet.Range("TemplateDir").Value &
"Forecast.xls"
Application.DisplayAlerts = True

--
taylorm
------------------------------------------------------------------------
taylorm's Profile: http://www.excelforum.com/member.php...o&userid=28892
View this thread: http://www.excelforum.com/showthread...hreadid=524999

--

Dave Peterson