how do you set a cell to auto save when populated



how do you set a macro to auto save an excel file when a certain cell is populated using the data in that cell to create a new file and save it to a specific location. I am losing my mind trying to get this to work. this is what I have so far.

(code)

Sub SaveAs()
Dim strFilename, strDirname, strPathname, strDefpath As String
On Error Resume Next ' If directory exist goto next line
strDirname = Range("C8").Value & Range("D8").Value ' New directory name

strFilename = Range("C8").Value & Range("D8").Value 'New file name
strDefpath = "R:\NewGunFits\\" & strFilename 'Default path name
If IsEmpty(strDirname) Then Exit Sub
If IsEmpty(strFilename) Then Exit Sub

MkDir strDefpath & strDirname
strPathname = strDefpath & strDirname & "\" & strFilename 'create total string

ActiveWorkbook.SaveAs Filename:=strPathname, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$C$8 & $D$8" Then

Call SaveAs

End If

End Sub

(/code)

Re: how do you set a cell to auto save when populated



A couple of items. First, do you know if the SaveAs code works independently of the Worksheet_Change event. Specifically, if you ran the SaveAs code alone, does it accomplish what you need? If so, then we just need to focus on the event that triggers the SaveAs code. If it does NOT do what you need already, then we first have to work with the code, and then with the event.

With regards to the Worksheet_Change event section of code, the snippet you have will NEVER trigger the SaveAs code. Target.Address will always refer to ONE specific cell address, i.e., $C$8. It will not show $C$8 & $D$8". If you need both cells to be populated, try having the code confirm that for you. One example:



copy to clipboard
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$8" or Target.address = "$D$8" then
If Range("C8") <> "" and Range("D8") <> "" then
Call SaveAs
End If
End If
End Sub

it is saving it and changing the name of the file but it is creating a new folder that I don't need and it is not going to the address that I need it to go to.