Cara cepat mengubah rumus/formula menjadi nilai/value di Excel menggunakan Macro

 Kode Macro :

Sub ConvertFormulasToValuesAndSaveToNewFolder()
    Dim sourceFolder As String, targetFolder As String
    Dim fileName As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim fDialog As FileDialog

    ' Pilih folder sumber
    Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
    MsgBox "Pilih folder SUMBER (berisi file Excel yang akan dikonversi)."
    If fDialog.Show <> -1 Then Exit Sub
    sourceFolder = fDialog.SelectedItems(1) & "\"

    ' Pilih folder tujuan
    MsgBox "Pilih folder TUJUAN (untuk menyimpan hasil konversi)."
    If fDialog.Show <> -1 Then Exit Sub
    targetFolder = fDialog.SelectedItems(1) & "\"

    ' Nonaktifkan notifikasi
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    ' Proses semua file Excel
    fileName = Dir(sourceFolder & "*.xls*")
    Do While fileName <> ""
        Set wb = Workbooks.Open(sourceFolder & fileName)

        ' Ubah semua worksheet jadi value-only
        For Each ws In wb.Worksheets
            With ws.UsedRange
                .Value = .Value
            End With
        Next ws

        ' Simpan dengan nama yang sama ke folder tujuan
        wb.SaveAs Filename:=targetFolder & fileName, FileFormat:=xlOpenXMLWorkbook
        wb.Close SaveChanges:=False
        fileName = Dir
    Loop

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

    MsgBox "Selesai! Semua file disimpan sebagai nilai saja ke folder tujuan.", vbInformation
End Sub

Cara pakai :

1. Tekan Alt + F11 → buka Visual Basic Editor
2. Pilih Insert → Module → paste kode di atas
3. Jalankan makro: ConvertFormulasToValuesAndSaveToNewFolder
4. Pilih folder sumber (file Excel dengan formula)
5. Pilih folder tujuan (untuk menyimpan hasil)
6. File akan dikonversi ke nilai-only dan disimpan ke folder tujuan dengan nama yang sama

Cara Edit Banyak File Excel dalam 1 Folder Menggunakan VBA Macro

 Sub RunOnAllFilesInFolder()  
   Dim folderName As String, eApp As Excel.Application, fileName As String  
   Dim wb As Workbook, ws As Worksheet, currWs As Worksheet, currWb As Workbook  
   Dim fDialog As Object: Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)  
   Set currWb = ActiveWorkbook: Set currWs = ActiveSheet  
   'Select folder in which all files are stored  
   fDialog.Title = "Select a folder"  
   fDialog.InitialFileName = currWb.Path  
   If fDialog.Show = -1 Then  
    folderName = fDialog.SelectedItems(1)  
   End If  
   'Create a separate Excel process that is invisibile  
   Set eApp = New Excel.Application: eApp.Visible = False  
   'Search for all files in folder [replace *.* with your pattern e.g. *.xlsx]  
   fileName = Dir(folderName & "\*.*")  
   Do While fileName <> ""  
     'Update status bar to indicate progress  
     Application.StatusBar = "Processing " & folderName & "\" & fileName  
     Set wb = Workbooks.Open(folderName & "\" & fileName)  
     '...  
     'YOUR CODE HERE  
     wb.Worksheets("NamaSheet").Activate  
     Range("A1:AA1000").Replace What:="Mei 2020", Replacement:="Desember 2022"  
     Range("A1:AA1000").Replace What:="Ronaldo", Replacement:="Messi"  
     Range("A1:AA1000").Replace What:="Portugal", Replacement:="Argentina"  
     '...  
     wb.Close SaveChanges:=True 'Close opened worbook saving, change as needed  
     Debug.Print "Processed " & folderName & "\" & fileName  
     fileName = Dir()  
   Loop  
   eApp.Quit  
   Set eApp = Nothing  
   'Clear statusbar and notify of macro completion  
   Application.StatusBar = ""  
   MsgBox "Completed executing macro on all workbooks"  
 End Sub