cara menghapus formula menjadi nilai value

  1. 10 bulan yang lalu

    Ass.... rekan2 master...
    Mohon dapat dibantu, saya ada file excel dengan beberapa sheet dimana cukup banyak formulanya tiap2 sheet.
    apabila saya mau membuat nilai value semua, saya harus lakukan tiap sheet berulang dengan metode copy paste value...
    apakah ada script yang bisa menghapus semua formula/rumus pada semua sheet cukup 1 tombol?

    terima kasih

  2. Caton

    17 Jan 2018 Terverifikasi Indonesia + 12.596 Poin

    Beberapa hal yang bisa mas @Nafis2006 lakukan:

    [1]. Coba direkam proses seleksi sampai copy paste dengan Record Macro. Hasil rekaman macro tersebut jika perlu diedit kembali dan disesuaikan dengan kebutuhan. Kemudian Assign Macro pada sebuah Form Button ke module hasil Record Macro tersebut.

    [2]. Buat sebuah Standard Module, dan masukkan script berikut:

    Option Explicit
    
    Public Sub Copas
       On Error Goto errHandler
       Selection.Copy
       Selection.PasteSpecial xlPasteValues
    
    errHandler:
       Err.Clear
       On Error Goto 0
    End Sub

    atau bisa juga areanya (range) didefinisikan secara langsung:

    Option Explicit
    
    Public Sub Copas
       On Error Goto errHandler
       ActiveSheet.Range("A2:B100").Copy
       ActiveSheet.Range("A2:B100").PasteSpecial xlPasteValues
    
    errHandler:
       Err.Clear
       On Error Goto 0
    End Sub

    [3] Bila format file yang akan diproses bukan Macro Enabled (misalnya file dengan ekstensi xlsx), scripting bisa dilakukan melalui Immediate Window (CTRL+G) pada VBE. Ketik script:

    Selection.Copy:Selection.PasteSpecial xlPasteValues

    atau

    ActiveSheet.Range("A2:B100").Copy:ActiveSheet.Range("A2:B100").PasteSpecial xlPasteValues

    kemudian tekan tombol Enter. Perhatikan, Immediate Window tidak dapat memproses Multiline Script. Jadi, script yang akan dieksekusi harus berada dalam satu baris. Antar script dipisahkan dengan tanda titik dua (:) seperti contoh di atas.

    Shoot031.png

    Untuk memudahkan proses selanjutnya, mas @Nafis2006 bisa membuat pintasan (Shortcut) dengan membuat Add-In dan Custom Toolbar (saya yakin mas @Nafis2006 sudah bisa melakukannya sendiri). Demikian ... ;)

  3. Terima kasih mas caton atas solusinya....
    saya sdh coba dah bisa dan berfungsi.... hanya sepertinya berfungsi pada sheet yang aktif saja yah...
    klo untuk semua sheet dalam file excel, ditambahkan script apa yah mas...

  4. Caton

    18 Jan 2018 Terverifikasi Indonesia + 12.596 Poin

    Apabila ingin digunakan pada seluruh Worksheet, bisa menggunakan pengulangan (misalnya FOR ... NEXT atau FOR EACH ... NEXT) untuk setiap Worksheet. Contohnya:

    Public Sub Copas()
        Dim xlSheet As Worksheet
        Dim sSheetName As String
        
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        
        sSheetName = "Sheet1|Sheet2|Sheet3"
        For Each xlSheet In ThisWorkbook.Worksheets
            If InStr(1, sSheetName, xlSheet.Name) Then
                With xlSheet
                    .Range("G7:I10").Copy
                    .Range("G7:I10").PasteSpecial xlPasteValues
                End With
            End If
        Next
    
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    End Sub

    Catatan: Pada contoh script di atas, proses Copy Paste hanya akan dilakukan terhadap Worksheet yang Sheet Tab Name-nya tercantum dalam variabel sSheetName. Jika akan dilakukan pada seluruh Worksheet, modifikasi menjadi:

    Public Sub Copas()
        Dim xlSheet As Worksheet
        
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        
        For Each xlSheet In ThisWorkbook.Worksheets
            With xlSheet
                .Range("G7:I10").Copy
                .Range("G7:I10").PasteSpecial xlPasteValues
            End With
        Next
    
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    End Sub

    Demikian contoh script yang bisa mas @Nafis2006 gunakan... ;)

  5. Terima kasih mas caton....
    Saya sudah coba berfungsi script nya.....

    kemudian saya coba masukan ke ribbon excel saya mas... kok tidak berfungsi yah...
    dan tidak muncul error.....

    saya sudah cek link nya sudah benar

    terima kasih

  6. Caton

    18 Jan 2018 Terverifikasi Indonesia + 12.596 Poin

    Mungkin belum ditambahkan parameter control As IRibbonControl menjadi:

    Public Sub Copas(Control As IRibbonControl)
    ...
    End Sub

  7. Mas caton...
    file terlampir..... sepertinya sdh saya kasih pk parameternya.....

    terima kasih

  8. Mohon maaf mas ada tambahan pertanyaan...
    untuk memasang password.... di VBA tersebut gimana yah mas...
    maaf saya masih belajar.....

    terima kasih

  9. Caton

    18 Jan 2018 Terverifikasi Indonesia + 12.596 Poin
    Di sunting 10 bulan yang lalu oleh Caton

    Untuk masalah pertama, ketika diletakkan sebagai Ribbon module, jangan gunakan objek ThisWorkbook untuk merujuk ke Workbook yang aktif. Merujuk ke ThisWorkbook artinya akan memproses ke dalam Add-In itu sendiri.. :) Gunakan objek ActiveWorkbook misalkan seperti:

    Sub Copas(control As IRibbonControl)    
        On Error Resume Next
        If Not Application.ActiveWorkbook Is Nothing Then
            Dim xlSheet As Worksheet
        
            Application.ScreenUpdating = False
            Application.EnableEvents = False
            
            For Each xlSheet In ActiveWorkbook.Worksheets
                With xlSheet
                    .Select
                    .Range("A1:B100").Copy
                    .Range("A1:B100").PasteSpecial xlPasteValues
                    .Range("A1").Select
                End With
            Next
        
            Application.CutCopyMode = False
            Application.ScreenUpdating = True
            Application.EnableEvents = True
        End If
        
        Err.Clear
        On Error GoTo 0
    End Sub

    Demikian... ;)

  10. Mantap mas caton.....
    terima kasih....sudah berfungsi..... iyah ternyata masalahnya disitu.

    maaf nih mau tanya lagi kalau range script yang ini .Range("A1:B100") yang B100 atau akhiran nama colomnya bisa dijadikan inputan atau jadi suatu variable yang bisa diganti secara form input yah mas?

    terima kasih, maaf merepotkan...

  11. Caton

    18 Jan 2018 Terverifikasi Indonesia + 12.596 Poin

    untuk memasang password.... di VBA tersebut gimana yah mas...

    Pada jendela VBE, klik menu Tools > VBAProject Properties... Pada jendela yang muncul, pilih tab Protection. Centang pada opsi Lock project for viewing... dan masukkan password. Kemudian tekan tombol Ok. Kemudian simpan (Save) file, lalu tutup aplikasi Excel. Kemudian buka kembali file Excelnya, lalu periksa di VBE apakah VBA Project-nya sudah terkunci...

    ... kalau range script yang ini .Range("A1:B100") yang B100 atau akhiran nama colomnya bisa dijadikan inputan atau jadi suatu variable yang bisa diganti secara form input ...

    Bisa saja. Definisi A1:B100 khan berupa string, jadi bisa diisi melalui sebuah variable... ;)

  12. Terima kasih mas caton....
    mohon maaf bru bisa respon.....laptopnya sempat error.......

    met sukses selalu....

 

atau Mendaftar untuk ikut berdiskusi!