Save-As with Cell Name

[sassriverrat]

hello-

I've been trying to write a piece of code to save-as a workbook. Here are the criteria- and I can fill in the "xxx's" if someone lays them out for me. Help is much appreciated.

Needs to save-as on a C:\Users\Bridge\Desktop\Noons\Noons-Arrivals\(Myfile)

The name needs to come from sheet "Notes" and Cells R8+R9+R10

I'd like a dialogue box to open up for the save location


thank you for your help. My attempts haven't worked.

 

[Nine Zero]

try something like this

Dim cPath As String
cPath = Sheets("Notes").Range("R8").Value & Sheets("Notes").Range("R9").Value & Sheets("Notes").Range("R10").Value
If MsgBox("Are you sure you want to save the file like so?" & vbNewLine & "C:\Users\Bridge\Desktop\Noons\Noons-Arrivals\" & cPath & ".xls", vbYesNo + vbQuestion, "Confirm") = vbYes Then
        ActiveWorkbook.SaveAs Filename:="C:\Users\Bridge\Desktop\Noons\Noons-Arrivals\" & cPath & ".xls", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
End If

 

[Richard U]

Above answer is best

 

[sassriverrat]

this looks really good. Only difficulty, I got a "Compile Error, Invalid Outside Procedure"

 

[Nine Zero]

Sorry I was under the assumption you already had a macro or some code and you just wanted to add to it.... try this out instead

Sub SavingMyFile()
Dim cPath As String
cPath = Sheets("Notes").Range("R8").Value & Sheets("Notes").Range("R9").Value & Sheets("Notes").Range("R10").Value
If MsgBox("Are you sure you want to save the file like so?" & vbNewLine & "C:\Users\Bridge\Desktop\Noons\Noons-Arrivals\" & cPath & ".xls", vbYesNo + vbQuestion, "Confirm") = vbYes Then
        ActiveWorkbook.SaveAs Filename:="C:\Users\Bridge\Desktop\Noons\Noons-Arrivals\" & cPath & ".xls", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
End If
End Sub

 

[sassriverrat]

I blindly deleted my code and copied yours in......going back and looking at it made all the difference.

Thank you for your help!

 

[Nine Zero]

lol no problem, glad i could help

 

[sassriverrat]

Now getting a run time error 1004

"Method 'SaveAs' of object '_Workbook' failed"

 

[Nine Zero]

re-check your path to make sure its right

 

[sassriverrat]

Fixed now. One other question- I changed the xls to xlsm which I believe caused it to corrupt the file. Am I correct in this presumption?

Secondly- it asks me about recalculating if an older version of excel (I am editing now using excel 16, but I've been writing the workbook on another machine with 10(?). Anyway, is there a way to have it auto-select "No" so that window doesn't pop up (the re-calc window, now the save window)