CODEWITHSUNDEEP
excelvba
Jamie Walker
Jamie Walker

Reputation: 213

How to make my Excel worksheet pull certain cell values for File name when I Save As?

Ok, I will try to explain this the best I can. I have an Excel workbook that will be used as a cost sheet. At the top of this worksheet will be a Proposal Number (in cell D3), Customer Name in cell D4), and System Size (in cell D5). So let's say my sheet is Proposal Number 4423 for Customer Shanghai Noon and System Size 2500. I would like when I click Save As for it to automatically name the file "4423 Shanghai Noon SCR-2500 Tsoc". Is there a way to do this with a Macro or any other way? Any advice is greatly appreciated.

Ok so this is the code I got from recording a macro but it keeps saying Compile error: Expected: end of statement. I don't know how to make it use the text from these cells in the Save As.

ActiveWorkbook.SaveAs Filename:= _
    "C:\Users\walkerja\Documents\CU Proposals\Range("D3").Select Range("D4").Select SCR-Range("D5").Select Tsoc.xlsm", FileFormat:= _
    xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Upvotes: 0

Views: 17084

Answers (1)

Stewbob
Stewbob

Reputation: 16899

Change your code to this:

Dim part1 As String
Dim part2 as String
Dim part3 as String

part1 = Range("D3").Value
part2 = Range("D4").Value
part3 = Range("D5").Value

ActiveWorkbook.SaveAs Filename:= _
"C:\Users\walkerja\Documents\CU Proposals\" & part1 & " " & part2 & " SCR-" & part3 & " Tsoc.xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

You were mixing string values with spreadsheet operation methods. You need to get the values of your cells first, then you can use those values to build a string for the file name.

Upvotes: 3

Related Questions