MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Change macro on save


Posted by David w on April 02, 2001 6:06 AM

I have a template that auto opens a data workbook
when it is opened and then returns to the template.
See below.
The problem I have I after you save the file under a
different name the next time you open the file you get
a run time error.
Can we have a macro that rewrites on save to change the
name (job20011)to the current filename?
Or is there a better way to open a workbook without
making it activate?

Sub AUTO_OPEN()
Workbooks.Open Filename:="I:\DATA\MATERIALS.xls"
Windows("job20011").Activate


Posted by Dave Hawley on April 02, 2001 10:37 AM


Hi David

Try this

Sub AUTO_OPEN()
Dim ThisBook as string
ThisBook=ThisWorkbook.Name
Workbooks.Open Filename:="I:\DATA\MATERIALS.xls"
Windows(ThisBook).Activate
End sub

Dave
OzGrid Business Applications

Posted by David W on April 02, 2001 1:03 PM

Re: thanks that did the trick!