Daren has attended:
Excel VBA Intro Intermediate course
Saving a new worksheet
The programming below creates a new file name from the information in the active workbook
The file name is created
The correct name is displayed in the save as window
But when the save button is selected the save as window disapears, but the workbook is not saved
Can to tell me what I have missed
Sub SaveReport()
Dim VisitNo As String
Dim SiteName As String
Dim SaveName As String
VisitNo = Sheets("DataSheet").Range("H31").Value
SiteName = Sheets("DataSheet").Range("H40").Value
SaveName = SiteName & "-Service Report-" & Date & "-Visit-" & VisitNo
Worksheets("ReportSheet").Activate
Application.GetSaveAsFilename (SaveName)
End Sub
RE: Saving a new worksheet
Hi Daren,
Thank you for your question.
There were two things I added to make the procedure work:
1. The first problem was the date format as you cannot use the / character when saving a filename.
2. I added a line of code to format the date to ddmmyy.
SaveName = SiteName & "-Service Report-" & Format(Date, "ddmmyy") & "-Visit-" & VisitNo
3. You called the application save as dialogue box to appear but didn't tell excel to save the active workbook.
4. I added ActiveWorkbook.SaveAs Filename:=SaveName
I also added a default location to save the file using the:
ChDir "C:\Users\Simon\Documents"
I hope this answers your question.
Regards
Simon
