VBA code that will save anytime sheet is updated and..

I need a vba for the below sample that will save the sheet every time it is update. also every time i save it says that workbook already exist and ask if i want to replace. I need a vba code that automatically says yes to thes questions.

Test 7.xlsm

Hi,

See if this code helps. Place it under the module for Sheet 1

Private Sub Worksheet_Calculate()

Application.DisplayAlerts = False
    ThisWorkbook.Save
Application.DisplayAlerts = True

End Sub

Thanks

is there a way to set this up to save to a specifice file folder.

why would you want that? you place the file or a copy in the folder you want and thats it. its not like you are saving copies of it.

I need to save it as the batch numner in cell D1 whilch will change everytime we start a new batch.

what if after i start new batch when ever i enter the batch number in D1 is saves it as that batch number to a certain file like (D:picking logs\)as the info in D1, then every time i run the macro you just gave me it just saves it to that? if that idea works do you know the macro to make it happen


Test 11.xlsm

Hello,

I think this will do the trick. Change the appropriate sections to fit your needs.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim txtLocation As String
Dim txtExtention As String
Dim txtFileName As String

Set Target = Range("D1")

txtLocation = "Z:\"         'Change this to the location for the files always with a \ at end
txtExtention = ".xlsm"      'The extention for the file
txtFileName = txtLocation & "Batch " & Target & txtExtention

'lets check of the file exists
    If ThisWorkbook.FullName = txtFileName Then
        'if the above is true then save the workbook
        Application.DisplayAlerts = False
            ThisWorkbook.Save
        Application.DisplayAlerts = True
    Else
        ThisWorkbook.SaveAs Filename:=txtFileName
    End If
    
End Sub

Thanks

Do i paste this into the sheet or into a module?

I entered the VBA into the workbook and keeps highlighting the below line in the code.

ThisWorkbook.SaveAs Filename:=txtFileName

TEST21.xlsm

Replace it with what was in the sheet.

Thanks

in my last reply i added the workbook with your code installed maybe you could check it out.

Hello,

The code under the workbook is not close to the code I provided. This is what was there

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False

ThisWorkbook.SaveAs "D:\picking logs\" & Sheets("Picking Log with colons").Range("D1").Value & ".xls"


Application.ScreenUpdating = True


End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Application.ScreenUpdating = False

ThisWorkbook.SaveAs "D:\picking logs\" & Sheets("Picking Log with colons").Range("D1").Value & ".xls"

Application.ScreenUpdating = True


End Sub

The code was supposed to be the codes from Post #2 and Post #7 I fixed it for you and also modified it to fit your needs ( change the saving location) I also fixed the extension to save it as an ".xls" I added another layer of security in case the folder does not exists

This is the final under Sheet1 Module:

Private Sub WorkSheet_Change(ByVal Target As Range)

Dim txtLocation As String
Dim txtExtension As String
Dim txtFileName As String
Dim response As Integer

Set Target = Range("D1")

txtLocation = "D:\picking logs\"         'Change this to the location for the files always with a \ at end
txtExtension = ".xls"      'The extention for the file
txtFileName = txtLocation & "Batch " & Target & txtExtension

'lets check of the file exists
    If ThisWorkbook.FullName = txtFileName Then
        'if the above is true then save the workbook
        Application.DisplayAlerts = False
            ThisWorkbook.Save
        Application.DisplayAlerts = True
    Else
        On Error GoTo Err1
        ThisWorkbook.SaveAs Filename:=txtFileName
    End If

Exit Sub
   
Err1:
MsgBox "An error has occurred. Make sure the directory in the macro is valid." & _
        vbNewLine & "Nothing was saved", vbCritical + vbOKOnly, "Error Saving File"
      
End Sub

Here is the final file

Attachment 263835

Thanks

WOW this is amazing thanks so much for your help.