This video illustrates how to write VBA code to Save an Excel File to a set network location with the File Name being a Cell Value on the Spreadsheet. In this example, we have a template containing daily sales figures for 4 different company divisions. The object is to create code that will save this daily file as the value of 2 cells on the spreadsheet. The 1st cell contains the most recent as of date and the 2nd cell contains the division name.

This example also displays how to set variable objects in VBA such as workbook and worksheet variables.



To get a copy of the workbook used in this video click on the link below:

https://theexcelcave.weebly.com/excel...

The Code used in this video is below:

Sub Daily_Report()

Dim wb1 As Workbook
Dim ws1 As Worksheet
Dim wb2 As Workbook
Dim Path As String
Dim FName As String

Set wb1 = ThisWorkbook
Set ws1 = wb1.Sheets("MTD Sales")

Path = "C:\Users\bradley\Desktop\Daily Sales\"
FName = Format(ws1.Range("H3"), "YYYYMMDD") & " " & ws1.Range("G3") & ".xlsx"

ws1.Range("B:D").Copy

Set wb2 = Workbooks.Add

With wb2.ActiveSheet.Range("B:D")

.PasteSpecial (xlValues)
.PasteSpecial (xlFormats)
End With
Application.DisplayAlerts = False
wb2.SaveAs Filename:=Path & FName
Application.DisplayAlerts = True
wb2.Close

End Sub