VBA Message Box to alter filename when saving

[Darren_workforce]

Hello,

I have a macro file that I setup for my boss that saves a file directly into her folder as "Queue count.xlsx". She then goes and renames it "Queue count [date].xlsx" so that she can keep historical records. My question is: is it possible to have a message box generate which would ask "Date?" and then once the date is entered, it is added onto the filename directly and saved accordingly? Below is the save code I have:

Sub new_workbook()

Worksheets(Array("Raw paste data", "Q breakdown", "Call vs Staff")).Copy
ActiveWorkbook.SaveAs "G:\Supervisors\Lisa\Queue count" & ".xlsx"

The date format she uses is "m.dd.yy"

 

[davesexcel]

Would the date happen to be in one of the spread sheets?

 

[Darren_workforce]

Would the date happen to be in one of the spread sheets?

Offhand, no there are no dates anywhere within the file itself. It's a prior workday, actually and with holidays and weekends, I would be concerned that the date would not always display accurately if I were to try and use a WORKDAY formula.

 

[Joe4]

Try this:

    Dim dt As String
    Dim fName As String
    
    dt = InputBox("Please enter file date in m.dd.yy format", "FILE DATE ENTRY")
    fName = "G:\Supervisors\Lisa\Queue count " & dt & ".xlsx"

    Worksheets(Array("Raw paste data", "Q breakdown", "Call vs Staff")).Copy
    ActiveWorkbook.SaveAs fName

 

[davesexcel]

This should work, since you are entering as a string, not an actual date.

Dim s As String
s = Application.InputBox("enter date")
Worksheets(Array("Raw paste data", "Q breakdown", "Call vs Staff")).Copy
ActiveWorkbook.SaveAs "G:\Supervisors\Lisa\Queue count" & s & ".xlsx"

 

[Darren_workforce]

Try this:

    Dim dt As String
    Dim fName As String
   
    dt = InputBox("Please enter file date in m.dd.yy format", "FILE DATE ENTRY")
    fName = "G:\Supervisors\Lisa\Queue count " & dt & ".xlsx"

    Worksheets(Array("Raw paste data", "Q breakdown", "Call vs Staff")).Copy
    ActiveWorkbook.SaveAs fName

That does exactly what I was hoping it would. Thank you, Joe!

 

[Joe4]

You are welcome.
Glad we were able to help!

 

[Darren_workforce]

This should work, since you are entering as a string, not an actual date.

Dim s As String
s = Application.InputBox("enter date")
Worksheets(Array("Raw paste data", "Q breakdown", "Call vs Staff")).Copy
ActiveWorkbook.SaveAs "G:\Supervisors\Lisa\Queue count" & s & ".xlsx"

The date box pops up but after I enter the date, the file gets saved as "queue count" but it doesn't add the date into the filename.