Save workbook to 2 different locations macro and rename one of them

[Buns1976]

Good Morning Everyone,

I'm trying to come up with a macro that will save a workbook in two different locations.This workbook gets used ONCE per day so for example, the original file resides on the desktop and I want to save that as the original file "C:\Users\Leasburg Front Count\Desktop\LOTTERY.XLSB" .

The 2nd part of the equation is a little stickier. I want to save that as "C:\Users\Leasburg Front Count\Dropbox\LOTTERY BACKUP\LOTTERY.XLSB"
BUT not overwrite the file from the previous save so maybe it saves as LOTTERY.XLSB(2), LOTTERY.XLSB(3), etc or what would be even better is
if we could save it as the current date so something like this LOTTERY2-25-19.XLSB OR something similar to that.

Thank you!

 

[mumps]

Re: How to save workbook to 2 different locations macro and rename one of them

My pleasure.

 

[Buns1976]

Re: How to save workbook to 2 different locations macro and rename one of them

Hi Mumps,
I'm having an issue with my people closing and opening the workbook which is overwriting my backup file.
Is there a way to add the TIME of DAY to the file name for example "LOTTERY_ 02-25-19 08:59" or something similar?

[COLOR=#333333]Code:[/COLOR]
[COLOR=#333333]Sub SaveToLocations()[/COLOR]
[COLOR=#333333]ActiveWorkbook.SaveAs Filename:="C:\Users\Leasburg Front Count\Desktop\LOTTERY.XLSB"[/COLOR]
[COLOR=#333333]ActiveWorkbook.SaveAs Filename:="C:\Users\Leasburg Front Count\Dropbox\LOTTERY BACKUP\LOTTERY" & Format(Date - 1, "mm-dd-yy") & ".XLSB"[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

 

[mumps]

Re: How to save workbook to 2 different locations macro and rename one of them

Try:

Sub SaveToLocations()
    ActiveWorkbook.SaveAs Filename:="C:\Users\Leasburg Front Count\Desktop\LOTTERY.XLSB"
    ActiveWorkbook.SaveAs Filename:="C:\Users\Leasburg Front Count\Dropbox\LOTTERY BACKUP\LOTTERY" & Format(Now() - 1, "mm-dd-yy hh:mm AMPM") & ".XLSB"
End Sub

 

[Buns1976]

Re: How to save workbook to 2 different locations macro and rename one of them

Hi Mumps,

I didn't like that code! I dropped the screenshot of the errors in the Dropbox folder you have access to!

Thanks

 

[mumps]

Re: How to save workbook to 2 different locations macro and rename one of them

Any reason why you didn't like the code? Did it not work?

 

[Buns1976]

Re: How to save workbook to 2 different locations macro and rename one of them

Hi Mumps,
Excel didn't like it! Not sure what the deal is. Were you able to see the screenshot of the errors?

Thank you!

 

[mumps]

Re: How to save workbook to 2 different locations macro and rename one of them

Give this a try:

Sub SaveToLocations()
    ActiveWorkbook.SaveAs Filename:="C:\Users\Leasburg Front Count\Desktop\LOTTERY.XLSB", FileFormat:=50
    ActiveWorkbook.SaveAs Filename:="C:\Users\Leasburg Front Count\Dropbox\LOTTERY BACKUP\LOTTERY" & Format(Now() - 1, "mm-dd-yy hh.mm AMPM") & ".XLSB", FileFormat:=50
End Sub

 

[Buns1976]

Re: How to save workbook to 2 different locations macro and rename one of them

That got it! Out of curiosity, what does "FileFormat:=50" represent?

Thanks a TON or TONNE in your speak!!

 

[mumps]

Re: How to save workbook to 2 different locations macro and rename one of them

"FileFormat:=50" saves the file in "xlsb" format. Also, there was another problem in the way the time was formatted. The original macro used a ":" as a separator between the hour and minutes. The semicolon is reserved by Excel so you can't use it as a part of a file name. You will notice that I changed it to a period.

 

[Buns1976]

Re: How to save workbook to 2 different locations macro and rename one of them

I did notice that. I tried a couple of things rather than the colon but couldn't get it to show the time!

Once again, THANK YOU SO MUCH!!