{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["PETUNJUK","DATA-MENTAH","LAPORAN-FORMAT","KODE-VBA"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":0,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"PETUNJUK","index":0,"n_rows":28,"n_cols":3,"rows_kept":28,"cut":false,"hidden":false,"merged":[[27,1,27,2],[22,1,22,2],[21,1,21,2],[13,1,13,2],[25,1,25,2],[17,1,17,2],[24,1,24,2],[2,1,2,2],[18,1,18,2],[19,1,19,2],[26,1,26,2],[11,1,11,2],[12,1,12,2],[20,1,20,2],[1,1,1,2],[15,1,15,2],[14,1,14,2],[10,1,10,2]]}
[]
[null,"EXCEL MACRO & VBA DASAR — OTOMATISASI LAPORAN BULANAN"]
[null,"PT Kopi Senja Nusantara · macro auto-format laporan penjualan bulanan"]
[]
[null,"ISI WORKBOOK:"]
[null,"PETUNJUK","Halaman ini — ikhtisar, legenda, dan cara menjalankan macro."]
[null,"DATA-MENTAH","Laporan penjualan MENTAH dari sistem kasir: tidak ada format, header tidak bold, angka tidak diformat, tidak ada total. Inilah kondisi 'sebelum macro'."]
[null,"LAPORAN-FORMAT","Sheet KOSONG. Macro akan meng-copy data dari DATA-MENTAH ke sini dan memformatnya: header bold dengan latar hijau, angka format Rp, baris zebra, kolom di-auto-width, dan menambahkan baris TOTAL di bawah."]
[null,"KODE-VBA","Source code VBA lengkap dari macro FormatLaporanBulanan — disalin ke sini sebagai referensi, supaya Anda bisa membaca kodenya tanpa harus membuka VBE."]
[]
[null,"CARA MENJALANKAN MACRO:"]
[null,"1. Buka sheet DATA-MENTAH untuk melihat data mentah. Buka sheet LAPORAN-FORMAT (kosong)."]
[null,"2. Klik tombol 'JALANKAN FORMAT' yang ada di sheet LAPORAN-FORMAT (sudah dibuatkan oleh workbook ini)."]
[null,"3. Atau jalankan manual: tekan ALT+F8 → pilih 'FormatLaporanBulanan' → Run."]
[null,"4. Macro akan memformat data dan menambah baris total. Selesai dalam < 1 detik."]
[null,"5. Untuk mengulang: hapus isi sheet LAPORAN-FORMAT lalu jalankan macro lagi."]
[]
[null,"CARA MELIHAT / EDIT KODE VBA:"]
[null,"1. Buka tab Developer (kalau belum ada: File → Options → Customize Ribbon → centang 'Developer')."]
[null,"2. Klik 'Visual Basic' (atau tekan ALT+F11) untuk membuka VBE (Visual Basic Editor)."]
[null,"3. Di panel kiri (Project Explorer), klik kanan 'VBAProject (vba-dasar-latihan.xlsm)' → Insert → Module."]
[null,"4. Kode sudah tersedia di sheet 'KODE-VBA' — copy seluruhnya, paste ke module baru di VBE."]
[null,"5. Tutup VBE. Macro siap dijalankan lewat ALT+F8 atau tombol."]
[]
[null,"CATATAN KEAMANAN:"]
[null,"• Saat pertama membuka file .xlsm, Excel memunculkan Security Warning 'Macros have been disabled'. Klik 'Enable Content' untuk mengaktifkan."]
[null,"• Macro dalam workbook ini aman — hanya memformat sel dan mengcopy data antar sheet, tidak mengakses file atau jaringan."]
[null,"• Jangan menyalin macro dari sumber tidak dipercaya tanpa membaca kodenya terlebih dahulu."]

{"sheet":"DATA-MENTAH","index":1,"n_rows":19,"n_cols":8,"rows_kept":19,"cut":false,"hidden":false,"merged":[[1,7,1,10],[0,7,0,10]]}
["Tgl","Produk","Cabang","Qty","Harga","Omzet",null,"↑ DATA INI MENTAH:"]
["2026-01-03","Espresso","Jakarta",120,25000,{"$f":"D2*E2","v":3000000},null,"Header tidak bold, angka telanjang, tidak ada total, kolom tidak proporsional. Inilah yang akan dirapikan macro."]
["2026-01-03","Latte","Jakarta",85,32000,2720000]
["2026-01-03","Cold Brew","Jakarta",60,28000,1680000]
["2026-01-04","Espresso","Bandung",95,25000,2375000]
["2026-01-04","Latte","Bandung",72,32000,2304000]
["2026-01-04","Cold Brew","Bandung",41,28000,1148000]
["2026-01-05","Espresso","Surabaya",110,25000,2750000]
["2026-01-05","Latte","Surabaya",68,32000,2176000]
["2026-01-05","Cold Brew","Surabaya",55,28000,1540000]
["2026-01-06","Espresso","Jakarta",130,25000,3250000]
["2026-01-06","Latte","Jakarta",90,32000,2880000]
["2026-01-06","Cold Brew","Jakarta",75,28000,2100000]
["2026-01-07","Espresso","Bandung",100,25000,2500000]
["2026-01-07","Latte","Bandung",80,32000,2560000]
["2026-01-07","Cold Brew","Bandung",50,28000,1400000]
["2026-01-08","Espresso","Surabaya",125,25000,3125000]
["2026-01-08","Latte","Surabaya",70,32000,2240000]
["2026-01-08","Cold Brew","Surabaya",62,28000,1736000]

{"sheet":"LAPORAN-FORMAT","index":2,"n_rows":10,"n_cols":2,"rows_kept":10,"cut":false,"hidden":false,"merged":[[2,1,2,6],[1,1,1,6],[4,1,5,6]]}
[]
[null,"LAPORAN PENJUALAN BULANAN — OUTPUT MACRO"]
[null,"Sheet ini akan diisi & diformat otomatis oleh macro FormatLaporanBulanan"]
[]
[null,"Klik tombol 'JALANKAN FORMAT' di bawah untuk menjalankan macro. Macro akan meng-copy data dari sheet DATA-MENTAH, memformat header (bold hijau), angka (Rp), baris zebra, dan menambah baris TOTAL."]
[]
[]
[]
[]
[null,"Output data mulai baris 11 ke bawah:"]

{"sheet":"KODE-VBA","index":3,"n_rows":5,"n_cols":2,"rows_kept":5,"cut":false,"hidden":false,"merged":[[2,1,2,1],[1,1,1,1]]}
[]
[null,"SOURCE CODE VBA — MACRO FormatLaporanBulanan"]
[null,"Salin seluruh kode di bawah (sel B5 s/d akhir) ke module VBE: klik kanan VBAProject → Insert → Module → paste."]
[]
[null,"' =========================================================\n' MACRO: FormatLaporanBulanan\n' Tujuan : Meng-copy data mentah dari sheet DATA-MENTAH\n'          ke sheet LAPORAN-FORMAT dan merapikannya.\n' Sumber : Modul standar (Insert > Module di VBE)\n' =========================================================\n\nSub FormatLaporanBulanan()\n\n    Dim wsRaw   As Worksheet\n    Dim wsOut   As Worksheet\n    Dim barisAkhir As Long\n    Dim i       As Long\n\n    ' 1. Tunjuk kedua sheet supaya mudah dirujuk\n    Set wsRaw = ThisWorkbook.Sheets(\"DATA-MENTAH\")\n    Set wsOut = ThisWorkbook.Sheets(\"LAPORAN-FORMAT\")\n\n    ' 2. Bersihkan output lama (mulai baris 11 ke bawah)\n    wsOut.Range(\"B11:G1000\").Clear\n\n    ' 3. Cari baris terakhir data mentah di kolom A\n    barisAkhir = wsRaw.Cells(wsRaw.Rows.Count, \"A\").End(xlUp).Row\n\n    ' 4. Tulis header di baris 11 sheet output\n    wsOut.Range(\"B11\").Value = \"Tanggal\"\n    wsOut.Range(\"C11\").Value = \"Produk\"\n    wsOut.Range(\"D11\").Value = \"Cabang\"\n    wsOut.Range(\"E11\").Value = \"Qty\"\n    wsOut.Range(\"F11\").Value = \"Harga\"\n    wsOut.Range(\"G11\").Value = \"Omzet\"\n\n    ' 5. Format header: bold, latar hijau, font putih\n    With wsOut.Range(\"B11:G11\")\n        .Font.Bold = True\n        .Font.Color = RGB(255, 255, 255)\n        .Interior.Color = RGB(0, 200, 83)\n        .HorizontalAlignment = xlCenter\n    End With\n\n    ' 6. Copy data baris demi baris dengan loop For\n    For i = 2 To barisAkhir\n        wsOut.Cells(i + 10, 2).Value = wsRaw.Cells(i, 1).Value   ' Tanggal\n        wsOut.Cells(i + 10, 3).Value = wsRaw.Cells(i, 2).Value   ' Produk\n        wsOut.Cells(i + 10, 4).Value = wsRaw.Cells(i, 3).Value   ' Cabang\n        wsOut.Cells(i + 10, 5).Value = wsRaw.Cells(i, 4).Value   ' Qty\n        wsOut.Cells(i + 10, 6).Value = wsRaw.Cells(i, 5).Value   ' Harga\n        wsOut.Cells(i + 10, 7).Value = wsRaw.Cells(i, 6).Value   ' Omzet\n    Next i\n\n    ' 7. Format angka: Qty ribuan, Harga & Omzet Rp\n    barisAkhir = barisAkhir + 10   ' geser sesuai offset output\n    wsOut.Range(\"E12:E\" & barisAkhir).NumberFormat = \"#,##0\"\n    wsOut.Range(\"F12:G\" & barisAkhir).NumberFormat = Chr(34) & \"Rp\" & Chr(34) & \"#,##0\"\n\n    ' 8. Baris zebra: selang-seling latar abu muda\n    For i = 12 To barisAkhir\n        If i Mod 2 = 0 Then\n            wsOut.Range(\"B\" & i & \":G\" & i).Interior.Color = RGB(245, 245, 245)\n        End If\n    Next i\n\n    ' 9. Tambah baris TOTAL dengan formula SUM\n    wsOut.Cells(barisAkhir + 1, 4).Value = \"TOTAL\"\n    wsOut.Cells(barisAkhir + 1, 5).Value = _\n        \"=SUM(E12:E\" & barisAkhir & \")\"\n    wsOut.Cells(barisAkhir + 1, 7).Value = _\n        \"=SUM(G12:G\" & barisAkhir & \")\"\n    With wsOut.Range(\"B\" & barisAkhir + 1 & \":G\" & barisAkhir + 1)\n        .Font.Bold = True\n        .Interior.Color = RGB(255, 245, 157)\n    End With\n\n    ' 10. Auto-fit lebar kolom supaya rapi\n    wsOut.Columns(\"B:G\").AutoFit\n\n    ' 11. Border tipis untuk seluruh tabel\n    wsOut.Range(\"B11:G\" & barisAkhir + 1).Borders.LineStyle = xlContinuous\n\n    MsgBox \"Laporan selesai diformat! \" & (barisAkhir - 11) & _\n           \" baris data diproses.\", vbInformation, \"FormatLaporanBulanan\"\n\nEnd Sub\n\n\n' =========================================================\n' MACRO BONUS: BersihkanOutput (untuk mengulang latihan)\n' =========================================================\nSub BersihkanOutput()\n    Dim wsOut As Worksheet\n    Set wsOut = ThisWorkbook.Sheets(\"LAPORAN-FORMAT\")\n    wsOut.Range(\"B11:G1000\").Clear\n    MsgBox \"Output dibersihkan. Jalankan FormatLaporanBulanan lagi.\", _\n           vbInformation, \"BersihkanOutput\"\nEnd Sub\n"]