VBA code to save as entire workbook in to same path with formulas value pasted

[Balajibenz]

Hi,

Can someone help me on how save the entire workbook in to same path as source workbook with the formulas alone value pasted and leaving all other formatting.

I am using blow line of code to save the file. Also help me to save the file even if there is an existing file in the path with same name.

ActiveWorkbook.SaveAs ActiveWorkbook.Path & "\Filename_" & Format(Date, "dd-mmm-yy") & ".xlsb", xlExcel12

 

[Joe4]

How many sheets do you have in the workbook?

 

[Balajibenz]

How many sheets do you have in the workbook?

4 sheets mate

 

[Joe4]

Try this:

Sub MySave()

    Dim ws As Worksheet
    
'   Look through each sheet and hard-code formulas
    For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
        ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
    Next ws
    
'   Save workbook
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs ActiveWorkbook.Path & "\Filename_" & Format(Date, "dd-mmm-yy") & ".xlsb", xlExcel12
    Application.DisplayAlerts = True
    
End Sub

 

[Balajibenz]

Try this:

Sub MySave()

    Dim ws As Worksheet
   
'   Look through each sheet and hard-code formulas
    For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
        ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
    Next ws
   
'   Save workbook
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs ActiveWorkbook.Path & "\Filename_" & Format(Date, "dd-mmm-yy") & ".xlsb", xlExcel12
    Application.DisplayAlerts = True
   
End Sub

Worked perfectly, thanks mate

 

[Joe4]

You are welcome.
Glad I was able to help!