Saving Workbook in Multiple Locations VBA

Question

I have two files:

X - File where I am running the Macro from. Y - File with template where I save to multiple locations.

How can I do:

Y.SaveAs Filename:="File1.xlsx"

Y.SaveAs Filename:="File2.xlsx"

Y.SaveAs Filename:="File3.xlsx"

The only solution I seem to find is using ActiveWorkbook after the first save but from what I read it is a bad habit to do so. Is there another way?

Edit: The Filenames are:= "File(" & SomeDateString & ").xlsx"

Code:

Dim Y as Workbook
Set Y = "C:\MyFiles\FileTemp.xlsx"
-X Generates Data to Y
Dim datenow as date
dim datestring as string
datenow = now
datestring = Format(datenow, "Short Date")
Application.DisplayAlerts = False
Y.SaveCopyas Filename:="C:\MyData1\Budget("& datestring &").xlsx"
Y.SaveCopyas Filename:="C:\MyData2\Budget("& datestring &").xlsx"
Y.SaveCopyas Filename:="C:\MyData3\Budget("& datestring &").xlsx"
Application.DisplayAlerts = True

Kyle Kyle · Accepted Answer · 2016-06-30T20:06:08

Set your workbook to a variable and use that.

Sub xxxx()
   Dim y as workbook

   Set y  = Workbooks("Workbook name")

   Y.SaveCopyAs Filename:="File(" & SomeDateString & ").xlsx"

   Y.SaveCopyAs Filename:="File(" & SomeDateString & ").xlsx"

   Y.SaveCopyAs Filename:="File(" & SomeDateString & ").xlsx"
End Sub

Saving Workbook in Multiple Locations VBA

2 Answers