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

Maybe:

"C:\Users\Leasburg Front Count\Dropbox\LOTTERY BACKUP\LOTTERY" & Format(Date, "mm-dd-yy") & ".XLSB"

 

[Buns1976]

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

Hi Mumps,

You think something like this?

Sub SaveToLocations()


 ActiveWorkbook.SaveCopyAs "C:\Users\Leasburg Front Count\Desktop\" + ActiveWorkbook.LOTTERY.XLSB
    ActiveWorkbook.SaveCopyAs ""C:\Users\Leasburg Front Count\Dropbox\LOTTERY BACKUP\LOTTERY" & Format(Date, "mm-dd-yy") & ".XLSB"
    ActiveWorkbook.Save
End Sub

 

[mumps]

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

Try:

Sub SaveToLocations()
    ActiveWorkbook.SaveAs "C:\Users\Leasburg Front Count\Desktop\" + ActiveWorkbook.LOTTERY.XLSB
    ActiveWorkbook.SaveAs "C:\Users\Leasburg Front Count\Dropbox\LOTTERY BACKUP\LOTTERY" & Format(Date, "mm-dd-yy") & ".XLSB"
End Sub

 

[mumps]

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

I didn't look at your first line of code. I have to be more careful. 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(Date, "mm-dd-yy") & ".XLSB"
End Sub

 

[Buns1976]

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

Thanks Mumps! That works great.

Question. I wasn't thinking clearly in my description.The date stamp needs to reflect the previous date if that's possible?
So today is the 25th and we are actually working on data from the 24th so if is possible we would want the backup to show "Lottery 2-24-19".

As always thanks so much!

 

[mumps]

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

Try:

Format(Date - 1, "mm-dd-yy")

 

[Buns1976]

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

THANKS MUMPS! That works great!

Also, wondering if you have any experience with Access. We use this workbook as a linked table in Access but I am ending up with
clutter because we don't use all of the rows in the worksheet on a daily basis so I'm looking for a way to do a select delete in an
Access table if a record is not populated based on a field.

Thanks

 

[mumps]

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

You are very welcome. I'm afraid that my knowledge of Access is extremely basic so I wouldn't be able to help.

 

[Buns1976]

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

MUMPS,

No worries and thanks again!