When you use a workbook, have you ever thought to save the Excel file based on a cell content? For example, you have “Sale price” in cell A1, and you would like to save the workbook as the name Sale price. Of course, you can type the name into the Save As dialog, and then save it. But this will be an inefficient method in your amount of work. Today, I will talk about a quick trick to solve it.
The following VBA code may help you to save your workbook with a specified cell value, please do as follows:
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Save Excel file with specific cell value
Private Sub filename_cellvalue()
Dim Path As String
Dim filename As String
Path = "C:\Users\dt\Desktop\my information\"
filename = Range("A1")
ActiveWorkbook.SaveAs filename:=Path & filename & ".xls", FileFormat:=xlNormal
3. And then press F5 key to execute the code, and your workbook has been named as the cell content of cell A1. See screenshot:
1. You can specify any cell value as the file name by just modifying the A1 to other cell reference in the above code.
2. Your active workbook will be saved into the folder of C:\Users\dt\Desktop\my information\ (the last slash must be remained when you paste your location to instead of it) location, you can change the path to your need.
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel
2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features
30-day free trial. 60-day money back guarantee.
However, my problem is I'd like to save a filename without leaving a space if the cell value is NULL for instance, if G6 value is blank then i like to the output "G4-H7 VTError".
Any there a way to approach this?
However, my problem is I'd like to save a filename without leaving a space if the cell value is NULL for instance, if G6 value is blank then i like to the output "G4-H7 VTError". Any there a way to approach this?
I have a control button for saving as PDF with a specific name to a specific location already working, but I want to modify the code as the filename is the name of the active sheet plus the value of a specific cell from another sheet. How do I do that? This is what I have for saving as PDF:
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
filename:="\\U:\Documents\My_IT_Stuff\T&A\PP11", _ OpenAfterPublish:=False
Application.ScreenUpdating = True
Hi, I am trying to use this code but when i try to execute I get a runtime 1004 error and it highlights row 7 as invalid: ActiveWorkbook.SaveAs filename:=Path & filename & ".xls", FileFormat:=xlNormal
How can I resolve this? Im using office 365
I have a problem and would be glad if there is someone to help me
I have a file at drive "F" and folder "ABS" - my filename is "Listdata"
In an Excel file on my desktop I want cells(4,4) to retieve data from "ABS" sheet(1) and cell (2,2)
The below code
activesheet.Cells(4, 4).Value = " F:\ABS\" & listdata & ".xlsx" & Sheets(1).Cells(2, 2).Value
but it does not work correctly
Please help... I've managed to do everything. But it saves the docs into My Documents instead of the specified server...
Private Sub CommandButton1_Click()
Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
Dim FileName3 As String
Dim FileName4 As String
Dim FileName5 As String
Path = "J:\Protection Services\Documents\Booysendal security"
FileName1 = Range("A2")
FileName2 = Range("B2")
FileName3 = Range("C2")
FileName4 = Range("D2")
FileName5 = Range("E2")
ActiveWorkbook.SaveAs Filename:=FileName1 & "-" & FileName2 & "-" & FileName3 & "-" & FileName4 & "-" & FileName5 & ".xls", FileFormat:=xlNormal
I need to save as a .csv file. how would I change this to make it save as a .csv file? Private Sub filename_cellvalue() 'Update 20141112 Dim Path As String Dim filename As String Path = "C:\Doorlister\import\DoorJob\" filename = Range("AC2") ActiveWorkbook.SaveAs filename:=Path & filename & ".xls", FileFormat:=xlNormal End Sub
I have a template that I don't want to be written over so that the user is required to save the file named by date entered into one of the fields. I don't want the user to have to open tools then select / run macro. They might just as well enter the filename.
you can add the =NOW() as a parameter to your filename in the cell before you extract it in the Macro (and also place a standard button in the corner of the page so that the emplyee wont have to open any tools
This is pretty cool... but there seems to be a bug that I'm not sure how to correct. I'm using this in a macro-enabled template. I open the template, I make changes/updates, I hit F5 to run the macro... and... a "Go To" dialog box pops up expecting me to key a "reference" in.
However, if I hit ALT F11, and THEN hit F5, the new .xls file is saved as expected.
Do I always need to open up the Visual Basic module to run this "save" procedure?