# VBA - Save worksheet only into a specific folder - name file based on cell value



## bored622 (Dec 15, 2022)

I'm trying to make a macro that when the user presses a button, it saves the worksheet based on a specific cell value and folder. Right now I got it to work so that it saves the entire workbook into the folder but I want the user to have the option to just save the worksheet itself. 

My code with saving the entire workbook.

Sub SavePTSFile()

Dim FileName As String
Dim Path As String
Application.DisplayAlerts = False
Path = "J:\Control_Center\PTS Distro Wave Files\"
FileName = Range("X3").Value & ".xlsx"
ActiveWorkbook.SaveAs Path & FileName, xlOpenXMLWorkbook
Application.DisplayAlerts = True


End Sub


----------



## HaHoBe (Dec 15, 2022)

Hi bored622,

how about


```
Sub SavePTSFile_mod()

Dim strFileName As String
Dim strPath As String

strPath = "J:\Control_Center\PTS Distro Wave Files\"
strFileName = Range("X3").Value & ".xlsx"

'check for file and delete
If Len(Dir(strPath & strFileName)) > 0 Then
  Kill strPath & strFileName
End If

ActiveSheet.Copy
ActiveWorkbook.SaveAs strPath & strFileName, xlOpenXMLWorkbook
'close workbook with save
'ActiveWorkbook.Close True

End Sub
```

Ciao,
Holger


----------



## bored622 (Dec 18, 2022)

HaHoBe said:


> Hi bored622,
> 
> how about
> 
> ...


Sorry for the late response, but what does the code do exactly? Just curious because i see a comment that says 'check for file and delete'.


----------



## HaHoBe (Dec 18, 2022)

Hi bored622,

please find the code with comments:


```
Sub SavePTSFile_mod()

Dim strFileName As String
Dim strPath As String

'/// drive and path to Folder for saving
strPath = "J:\Control_Center\PTS Distro Wave Files\"
'/// get the filename from ActiveSheet Cell X3 and append dot and extension for saving
strFileName = Range("X3").Value & ".xlsx"

'/// check for any existing file at the given ülace using Dir
If Len(Dir(strPath & strFileName)) > 0 Then
  '/// delete the existing file without putting it into the RecycleBin
  Kill strPath & strFileName
End If

'/// copy the ActiveSheet as a new workbook
ActiveSheet.Copy
'/// save the ActiveWorkbook at folder and with name as indicated as macrofree workbook
ActiveWorkbook.SaveAs strPath & strFileName, xlOpenXMLWorkbook
'/// as the workbook stays open you may close it with saving (True) or without (False)
'close workbook with save
'ActiveWorkbook.Close True

End Sub
```

Normally you will get a notification if you try and save a file to a folder where a file of that name already exists (same goes for deleting sheets or closing a workbook without saving where you altered values or use a volatile function). You may turn the notifications off by using `Application.DisplayAlerts = False` but you should turn them on again at the end of the code with `Application.DisplayAlerts = True`. The code above checks for the existence of a file and if so will delete it permanently (there will be no information from Excel).

HTH,
Holger


----------



## bored622 (Dec 18, 2022)

HaHoBe said:


> Hi bored622,
> 
> please find the code with comments:
> 
> ...


Will this keep the workbook I originally had open when I run this macro, or will it close it out after I have typed something in cell X3?


----------



## bored622 (Dec 18, 2022)

HaHoBe said:


> Hi bored622,
> 
> please find the code with comments:
> 
> ...


Just tested it and answered my own question (haha)! Thank you so much for the help. It worked perfectly!


----------

