I have a good Save VBA code i use to save stuff and build folders, But it has Input Required(help)

[zone709]

This is a very good vba code to build folders and save stuff. I use it all the time, but I want to use this code on a click of a button to save it to a folder I prefer without using --> SaveName = Trim(InputBox("Enter the file name you want to save. (blank to skip)", "Input required."))

What do I need to remove or add to this code below for that to work thanks.

So basically I need to remove that and have an area in code to name sheet and just save it to a folder I name. While using this code without the Input required box. Thanks.

Sub SaveFileButton()
Dim SaveName As String
Const MyPath As String = "C:\Test\" 'This is the path it's using for saves before the dates
ReName:
On Error GoTo ErrorHandle


SaveName = Trim(InputBox("Enter the file name you want to save. (blank to skip)", "Input required."))
If Len(SaveName) > 0 Then
SaveName = SaveName & ".xlsx"
If Len(Dir(MyPath & Format(Now, "yy") & "-" & Format$(Now, "mmm"), vbDirectory)) = 0 Then
MkDir MyPath & Format(Now, "yy") & "-" & Format$(Now, "mmm") & "\"
End If
If Len(Dir(MyPath & Format(Now, "yy") & "-" & Format$(Now, "mmm") & "\" & Format(Now, "mm") & "-" & Format(Now, "dd") & "-" & Format(Now, "yyyy"), vbDirectory)) = 0 Then
MkDir MyPath & Format(Now, "yy") & "-" & Format$(Now, "mmm") & "\" & Format(Now, "mm") & "-" & Format(Now, "dd") & "-" & Format(Now, "yyyy") & "\"
End If
ActiveWorkbook.SaveAs Filename:=MyPath & Format(Now, "yy") & "-" & Format$(Now, "mmm") & "\" & Format(Now, "mm") & _
"-" & Format(Now, "dd") & "-" & Format(Now, "yyyy") & "\" & SaveName, FileFormat:=xlOpenXMLWorkbook
End If

Exit Sub

ErrorHandle:
If Err.Number = 75 Then
Resume Next
ElseIf Err.Number = 1004 Then
MsgBox ("That name is already used for this day. Please try again!")
GoTo ReName
Else: MsgBox ("There is an unknown error")
End If
End Sub

 

[Joe4]

What if, in taking your original code, you simply replace this:

SaveName = Trim(InputBox("Enter the file name you want to save. (blank to skip)", "Input required."))

with the hard-coded value that you want your file named, such as:

SaveName = "TestFile1"

Then, assuming that you original code was working before, the rest of your code should follow and work.

 

[zone709]

ok almost there. its working i cant believe that was it. That easy i think i was thinking to much and either taking to much away or putting too much data in. So as i use it works, but i get the excel pop up that. im saving in a not enabled macro workbook? Am i missing something small in the code like a .xlsm or .xls or anything that's giving me that pop up? thanks

 

[Joe4]

See my post #9. Since there is VBA code in it, you will need to save it as a Macro-Enabled workbook.
So you will need to change the file extension to "xlsm", and you will need to to change the FileFormat argument in your Save statement to match the one I showed in post #9.

How did I know what the FileFormat value should be? Easy way to find out. Record yourself saving a Macro Enabled Workbook, and look at the top that results.

 

[zone709]

I got it joe i fixed it thanks for the help.