Save a workbook in 2 places

[Mike Fogleman]

I have a workbook that I constantly update with web queries throughout the
day. This is on my pc at work on my C:\ drive. I also keep a copy on a
public network drive so co-workers can see the latest & greatest updates.
What I want to do is whenever I save the book it will save to both places.
Since I rarely close this book throughout the week, I just do saves after
each update, I stayed away from the Before_Close event, and went with the
Before_Save event. My code will save to the network drive, but crashes Excel
at the end of the procedure, and therefore does not save to the local drive.
I thought perhaps because I was saving the file with the same name in 2
places, it crashed because of duplicate file names. So I added "Copy of" to
the name of the network file, but still no cookie. It still crashes. Can
anybody point me in the right direction so when I click the Save (Disk)
icon, it will save to both drives? I put the CurDir check in the code so
that the network copy would not also run the code in case someone tried to
save that file.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim CrDr As String
CrDr = CurDir
If CurDir = "C:\My Documents" Then
ChDir "\\Lafbdc1\dept\NCP"
Application.EnableEvents = False
ActiveWorkbook.SaveAs "\\Lafbdc1\dept\NCP\Copy of NCP Work.xls"
ChDir CrDr
End If
Application.EnableEvents = True
End Sub

Mike F

 

[Dave Peterson]

I think if I were doing this, I'd stay away from the events and just provide a
macro that would save the workbook twice:

option explicit
sub SaveMeTwice()
with thisworkbook
.save
.savecopyas filename:="\\Lafbdc1\dept\NCP\Copy of NCP Work.xls"
end with
end sub

Maybe put a button on the worksheet and assign the macro to that button.


ps. Your code blew up for me, too.

The chdir won't work with UNC paths--so you don't need it. And after the
workbook saves as the new name, workbook that is open would be that "Copy of..."
workbook. I'm not sure if that's what you really wanted anyway.

pps. After I deleted the chdir stuff, the code still crashed.

(I used xl2003 in my testing (winXP home).)

 

[Mike Fogleman]

Thanks for the effort, Dave. I'm still not sure why it blows up. The code
started with a simple 'SaveAs' and I kept adding stuff to it trying to
figure out the key. I may end up taking your advice. I have a custom menu I
could add a Save item to. Hey! Better yet, I could put it on my Personal.xls
and use it to save my other workbooks that have copies on the network. And
the code wouldn't be in either copy. There, I'm glad we had this talk.

Mike F

 

[Dave Peterson]

Sometimes just saying things out loud will get you to a solution.