Download Cara Membuat Laporan Koperasi Simpan Pinjam Dengan Excel 2017
Posted on by admin
Jan 20, 2017 Contoh buku koperasi simpan pinjam Excel bisa Anda lihat melalui internet. Nantinya, hal tersebut bisa Anda jadikan sebagai acuan dalam melakukan pembukuan koperasi. Ada banyak hal yang bisa Anda dapatkan dengan melakukan pembukuan atau laporan keuangan dari koperasi serba usaha ataupun koperasi simpan pinjam.
- Download Cara Membuat Laporan Koperasi Simpan Pinjam Dengan Excel 2017 Pdf
- Download Cara Membuat Laporan Koperasi Simpan Pinjam Dengan Excel 2017 Tutorial
- Download Cara Membuat Laporan Koperasi Simpan Pinjam Dengan Excel 2017 Free
- Dalam aplikasi kali ini laporan dalam bentuk excel, semua transaksi akan terekan pada tabel transaksi, termasuk data anggota, simpanan anggota, pinjaman anggota dan angsuran. Memang program ini sangat membantu sekali, pekerjaan akan lebih mudah karena semua terintegritas. Mulai dari sheet input anggota - perjanjian simpanan, surat pernyataan, sampai laporan, semua hanya butuk waktu kurang dari.
- Jika koperasi Simpan Pinjam anda cukup besar dan berkembang, anda bisa menggunakan software Profesional yang dirancang khusus dengan sangat lengkap dimulai dari: Pendataan Anggota Pengaturan Simpanan dan Pinjaman Akuntansi Lengkap Laporan yang detail Pembagian SHU Berdasarkan Anggaran Dasar Koperasi dan Pembagian SHU per Anggota Detail berdasrkan kontribusi anggota terhadap Koperasi.
Contoh txtNIK.Value = ' berarti textboxnya txtNIK
Untuk perintah BATAL kode VBAnya
Private Sub cmdBatal_Click()
txtNIK.Value = '
cboRST.Value = '
TxtNOANG.Value = '
TxtTGA.Value = '
txtNM.Value = '
txtTMP.Value = '
txtTGL.Value = '
txtJR.Value = '
txtKC.Value = '
TxtANG.Value = '
TxtJAM.Value = '
cboBANK.Value = '
txtNOBANK.Value = '
TxtBAYARDI.Value = '
txtKJ.Value = '
txtTGLP.Value = '
TxtPINJ.Value = '
optLAMA.Value = '
optBARU.Value = '
optDINAS.Value = '
optPENSIUNAN.Value = '
optUMUM.Value = '
optATM.Value = '
TxtKE.Value = '
txtHP.Value = '
Cbobayar.Value = '
TxtGaji.Value = '
JMGAJI.Value = '
End Sub
Untuk perintah ANGGOTA BARU
Private Sub cmdInput_Click()On Error Resume Next
Dim Filter As String, Title As String, FileX As String
Dim CellTujuan As Long
If txtNIK.Value = ' Then
MsgBox 'NOMOR masih kosong', vbOKOnly
txtNIK.SetFocus
Exit Sub
ElseIf txtNM.Value = ' Then
MsgBox 'NAMA masih kosong', vbOKOnly
txtNM.SetFocus
Exit Sub
Exit Sub
End If
With Worksheets('DATA')
CellTujuan = .Cells(.Rows.Count, 'D'). _
End(xlUp).Offset(0, 1).Row
'--- data input
Worksheets('DATA').Cells(CellTujuan + 1, 1).Value = CellTujuan
Worksheets('DATA').Cells(CellTujuan + 1, 2).Value = (cboRST.Value + TxtNOANG.Value)
Worksheets('DATA').Cells(CellTujuan + 1, 3).Value = txtNIK.Value
Worksheets('DATA').Cells(CellTujuan + 1, 4).Value = cboRST.Value
Worksheets('DATA').Cells(CellTujuan + 1, 5).Value = TxtNOANG.Value
Worksheets('DATA').Cells(CellTujuan + 1, 6).Value = TxtTGA.Value
Worksheets('DATA').Cells(CellTujuan + 1, 7).Value = txtNM.Value
Worksheets('DATA').Cells(CellTujuan + 1, 8).Value = txtTMP.Value
Worksheets('DATA').Cells(CellTujuan + 1, 9).Value = txtTGL.Value
Worksheets('DATA').Cells(CellTujuan + 1, 10).Value = txtJR.Value
Worksheets('DATA').Cells(CellTujuan + 1, 11).Value = txtKC.Value
Worksheets('DATA').Cells(CellTujuan + 1, 12).Value = TxtANG.Value
Worksheets('DATA').Cells(CellTujuan + 1, 13).Value = TxtJAM.Value
Worksheets('DATA').Cells(CellTujuan + 1, 14).Value = cboBANK.Value
Worksheets('DATA').Cells(CellTujuan + 1, 15).Value = txtNOBANK.Value
Worksheets('DATA').Cells(CellTujuan + 1, 16).Value = TxtBAYARDI.Value
Worksheets('DATA').Cells(CellTujuan + 1, 17).Value = txtKJ.Value
Worksheets('DATA').Cells(CellTujuan + 1, 18).Value = txtTGLP.Value
Worksheets('DATA').Cells(CellTujuan + 1, 19).Value = TxtPINJ.Value
If optLAMA = True Then
Worksheets('DATA').Cells(CellTujuan + 1, 20).Value = 'LAMA'
ElseIf optBARU = True Then
Worksheets('DATA').Cells(CellTujuan + 1, 20).Value = 'BARU'
End If
If optDINAS = True Then
Worksheets('DATA').Cells(CellTujuan + 1, 21).Value = 'DINAS'
ElseIf optPENSIUNAN = True Then
Worksheets('DATA').Cells(CellTujuan + 1, 21).Value = 'PENSIUNAN'
ElseIf optUMUM = True Then
Worksheets('DATA').Cells(CellTujuan + 1, 21).Value = 'UMUM'
ElseIf optATM = True Then
Worksheets('DATA').Cells(CellTujuan + 1, 21).Value = 'ATM'
End If
Worksheets('DATA').Cells(CellTujuan + 1, 22).Value = TxtKE.Value
Worksheets('DATA').Cells(CellTujuan + 1, 23).Value = txtHP.Value
Worksheets('DATA').Cells(CellTujuan + 1, 24).Value = Cbobayar.Value
Worksheets('DATA').Cells(CellTujuan + 1, 25).Value = TxtGaji.Value
Worksheets('DATA').Cells(CellTujuan + 1, 26).Value = '=DATEDIF(RC[-17],NOW(),'y')&' '&'Tahun''
Worksheets('DATA').Cells(CellTujuan + 1, 27).Value = JMGAJI.Value
End With
With Worksheets('BLANGKO')
Worksheets('BLANGKO').Cells(2, 1) = txtNM.Value
Worksheets('BLANGKO').Cells(2, 2) = txtTMP.Value
Worksheets('BLANGKO').Cells(2, 3) = TxtTGA.Value
Worksheets('BLANGKO').Cells(2, 4) = txtKJ.Value
Worksheets('BLANGKO').Cells(2, 5) = txtJR.Value
Worksheets('BLANGKO').Cells(2, 6) = txtKC.Value
Worksheets('BLANGKO').Cells(2, 7) = TxtPINJ.Value
Worksheets('BLANGKO').Cells(2, 8) = '=TERBILANG(RC[-1])'
Worksheets('BLANGKO').Cells(2, 9) = cboBANK.Value
Worksheets('BLANGKO').Cells(2, 10) = txtNOBANK.Value
Worksheets('BLANGKO').Cells(2, 11) = cboRST.Value
Worksheets('BLANGKO').Cells(2, 12) = TxtANG.Value
Worksheets('BLANGKO').Cells(2, 13) = TxtJAM.Value
Worksheets('BLANGKO').Cells(2, 14) = txtTGLP.Value
Worksheets('BLANGKO').Cells(2, 15) = (TxtPINJ.Value / 12)
Worksheets('BLANGKO').Cells(2, 16) = '=TERBILANG(RC[-1])'
Worksheets('BLANGKO').Cells(2, 17) = '=RC[-3]+30'
Worksheets('BLANGKO').Cells(2, 18) = TxtNOANG.Value
End With
With Worksheets('BERKAS')
Worksheets('BERKAS').Cells(3, 11) = TxtNOANG.Value
Worksheets('BERKAS').Cells(11, 11) = cboRST.Value
Worksheets('BERKAS').Cells(7, 2) = TxtPINJ.Value
Worksheets('BERKAS').Cells(10, 5) = txtNM.Value
Worksheets('BERKAS').Cells(11, 3) = txtJR.Value
Worksheets('BERKAS').Cells(12, 3) = txtKC.Value
Worksheets('BERKAS').Cells(13, 5) = TxtANG.Value
Worksheets('BERKAS').Cells(14, 5) = TxtJAM.Value
Worksheets('BERKAS').Cells(15, 5) = (cboBANK.Value + txtNOBANK.Value)
End With
With Worksheets('KARTU')
Worksheets('KARTU').Cells(2, 3) = TxtPINJ.Value
Worksheets('KARTU').Cells(3, 1) = '=TERBILANG(R[-1]C[2])'
Worksheets('KARTU').Cells(7, 5) = txtNM.Value
Worksheets('KARTU').Cells(8, 5) = txtTMP.Value
Worksheets('KARTU').Cells(8, 6) = txtTGL.Value
Worksheets('KARTU').Cells(8, 7) = '=DATEDIF(RC[-1],NOW(),'y')&' '&'Tahun''
Worksheets('KARTU').Cells(9, 5) = txtJR.Value
Worksheets('KARTU').Cells(10, 5) = txtKC.Value
Worksheets('KARTU').Cells(12, 5) = cboRST.Value
Worksheets('KARTU').Cells(7, 10) = TxtNOANG.Value
Worksheets('KARTU').Cells(9, 10) = TxtBAYARDI.Value
Worksheets('KARTU').Cells(10, 10) = Cbobayar.Value
Worksheets('KARTU').Cells(12, 10) = TxtANG.Value
Worksheets('KARTU').Cells(13, 9) = TxtJAM.Value
Worksheets('KARTU').Cells(13, 7) = txtNOBANK.Value
Worksheets('KARTU').Cells(16, 2) = txtTGLP.Value
Worksheets('KARTU').Cells(16, 8) = TxtPINJ.Value
Worksheets('KARTU').Cells(11, 6) = txtKJ.Value
Application.ScreenUpdating = False
NamaFile = (cboRST.Value + TxtNOANG.Value + ' ' + txtNM.Value)
FileX = ActiveWorkbook.Path & 'Photo' & NamaFile & '.jpg'
Worksheets('KARTU').Image1.Picture = LoadPicture(FileX)
Application.ScreenUpdating = True
End With
With Worksheets('BALEK KARTU')
Worksheets('BALEK KARTU').Cells(9, 3) = JMGAJI.Value
Worksheets('BALEK KARTU').Cells(10, 2) = '=terbilang(R[-1]C[1])'
Worksheets('BALEK KARTU').Cells(18, 2) = txtNM.Value
Worksheets('BALEK KARTU').Cells(19, 3) = txtTGLP.Value
End With
With Worksheets('KWITANSI')
Worksheets('KWITANSI').Cells(2, 5) = cboRST.Value
Worksheets('KWITANSI').Cells(23, 5) = cboRST.Value
Worksheets('KWITANSI').Cells(2, 6) = TxtNOANG.Value
Worksheets('KWITANSI').Cells(23, 6) = TxtNOANG.Value
Worksheets('KWITANSI').Cells(12, 10) = txtTGLP.Value
Worksheets('KWITANSI').Cells(35, 10) = txtTGLP.Value
Worksheets('KWITANSI').Cells(18, 14) = txtNM.Value
Worksheets('KWITANSI').Cells(18, 14) = txtNM.Value
Worksheets('KWITANSI').Cells(6, 8) = '=TERBILANG(R[12]C[-3])'
Worksheets('KWITANSI').Cells(27, 8) = '=TERBILANG(R[12]C[-3])'
Worksheets('KWITANSI').Cells(18, 5) = TxtPINJ.Value
Worksheets('KWITANSI').Cells(39, 5) = TxtPINJ.Value
End With
Select Case cboRST.Value
Case 'ANGGREK'
With Worksheets('ANGGREK')
CellTujuan = .Cells(.Rows.Count, 'D'). _
End(xlUp).Offset(0, 1).Row
'--- data input
Worksheets('ANGGREK').Cells(CellTujuan + 1, 1).Value = CellTujuan - 1
Worksheets('ANGGREK').Cells(CellTujuan + 1, 2).Value = txtTGLP.Value
Worksheets('ANGGREK').Cells(CellTujuan + 1, 3).Value = (Day(txtTGLP.Value))
Worksheets('ANGGREK').Cells(CellTujuan + 1, 4).Value = TxtNOANG.Value
Worksheets('ANGGREK').Cells(CellTujuan + 1, 5).Value = txtNM.Value
If optDINAS = True Then
Worksheets('ANGGREK').Cells(CellTujuan + 1, 6).Value = 1
ElseIf optPENSIUNAN = True Then
Worksheets('ANGGREK').Cells(CellTujuan + 1, 7).Value = 1
ElseIf optUMUM = True Then
Worksheets('ANGGREK').Cells(CellTujuan + 1, 8).Value = 1
ElseIf optATM = True Then
Worksheets('ANGGREK').Cells(CellTujuan + 1, 8).Value = 1
End If
If optLAMA = True Then
Worksheets('ANGGREK').Cells(CellTujuan + 1, 10).Value = 1
ElseIf optBARU = True Then
Worksheets('ANGGREK').Cells(CellTujuan + 1, 11).Value = 1
End If
If optLAMA = True Then
Worksheets('ANGGREK').Cells(CellTujuan + 1, 12).Value = TxtPINJ.Value
ElseIf optBARU = True Then
Worksheets('ANGGREK').Cells(CellTujuan + 1, 13).Value = TxtPINJ.Value
End If
Worksheets('ANGGREK').Cells(CellTujuan + 1, 14).Value = (TxtPINJ.Value * 3 / 100)
Worksheets('ANGGREK').Cells(CellTujuan + 1, 15).Value = '=IF(RC[-4]=1,5000,'')'
Worksheets('ANGGREK').Cells(CellTujuan + 1, 16).Value = '=SUM(RC[-4]:RC[-3])-SUM(RC[-2]:RC[-1])'
End With
Case 'MAWAR'
With Worksheets('MAWAR')
CellTujuan = .Cells(.Rows.Count, 'D'). _
End(xlUp).Offset(0, 1).Row
'--- data input
Worksheets('MAWAR').Cells(CellTujuan + 1, 1).Value = CellTujuan - 1
Worksheets('MAWAR').Cells(CellTujuan + 1, 2).Value = txtTGLP.Value
Worksheets('MAWAR').Cells(CellTujuan + 1, 3).Value = (Day(txtTGLP.Value))
Worksheets('MAWAR').Cells(CellTujuan + 1, 4).Value = TxtNOANG.Value
Worksheets('MAWAR').Cells(CellTujuan + 1, 5).Value = txtNM.Value
If optDINAS = True Then
Worksheets('MAWAR').Cells(CellTujuan + 1, 6).Value = 1
ElseIf optPENSIUNAN = True Then
Worksheets('MAWAR').Cells(CellTujuan + 1, 7).Value = 1
ElseIf optUMUM = True Then
Worksheets('MAWAR').Cells(CellTujuan + 1, 8).Value = 1
ElseIf optATM = True Then
Worksheets('MAWAR').Cells(CellTujuan + 1, 8).Value = 1
End If
If optLAMA = True Then
Worksheets('MAWAR').Cells(CellTujuan + 1, 10).Value = 1
ElseIf optBARU = True Then
Worksheets('MAWAR').Cells(CellTujuan + 1, 11).Value = 1
End If
If optLAMA = True Then
Worksheets('MAWAR').Cells(CellTujuan + 1, 12).Value = TxtPINJ.Value
ElseIf optBARU = True Then
Worksheets('MAWAR').Cells(CellTujuan + 1, 13).Value = TxtPINJ.Value
End If
Worksheets('MAWAR').Cells(CellTujuan + 1, 14).Value = (TxtPINJ.Value * 3 / 100)
Worksheets('MAWAR').Cells(CellTujuan + 1, 15).Value = '=IF(RC[-4]=1,5000,'')'
Worksheets('MAWAR').Cells(CellTujuan + 1, 16).Value = '=SUM(RC[-4]:RC[-3])-SUM(RC[-2]:RC[-1])'
End With
Case ('TERATAI')
With Worksheets('TERATAI')
CellTujuan = .Cells(.Rows.Count, 'D'). _
End(xlUp).Offset(0, 1).Row
'--- data input
Worksheets('TERATAI').Cells(CellTujuan + 1, 1).Value = CellTujuan - 1
Worksheets('TERATAI').Cells(CellTujuan + 1, 2).Value = txtTGLP.Value
Worksheets('TERATAI').Cells(CellTujuan + 1, 3).Value = (Day(txtTGLP.Value))
Worksheets('TERATAI').Cells(CellTujuan + 1, 4).Value = TxtNOANG.Value
Worksheets('TERATAI').Cells(CellTujuan + 1, 5).Value = txtNM.Value
If optDINAS = True Then
Worksheets('TERATAI').Cells(CellTujuan + 1, 6).Value = 1
ElseIf optPENSIUNAN = True Then
Worksheets('TERATAI').Cells(CellTujuan + 1, 7).Value = 1
ElseIf optUMUM = True Then
Worksheets('TERATAI').Cells(CellTujuan + 1, 8).Value = 1
ElseIf optATM = True Then
Worksheets('TERATAI').Cells(CellTujuan + 1, 8).Value = 1
End If
If optLAMA = True Then
Worksheets('TERATAI').Cells(CellTujuan + 1, 10).Value = 1
ElseIf optBARU = True Then
Worksheets('TERATAI').Cells(CellTujuan + 1, 11).Value = 1
End If
If optLAMA = True Then
Worksheets('TERATAI').Cells(CellTujuan + 1, 12).Value = TxtPINJ.Value
ElseIf optBARU = True Then
Worksheets('TERATAI').Cells(CellTujuan + 1, 13).Value = TxtPINJ.Value
End If
Worksheets('TERATAI').Cells(CellTujuan + 1, 14).Value = (TxtPINJ.Value * 3 / 100)
Worksheets('TERATAI').Cells(CellTujuan + 1, 15).Value = '=IF(RC[-4]=1,5000,'')'
Worksheets('TERATAI').Cells(CellTujuan + 1, 16).Value = '=SUM(RC[-4]:RC[-3])-SUM(RC[-2]:RC[-1])'
End With
End Select
txtNIK.Value = '
cboRST.Value = '
TxtNOANG.Value = '
TxtTGA.Value = '
txtNM.Value = '
txtTMP.Value = '
txtTGL.Value = '
txtJR.Value = '
txtKC.Value = '
TxtANG.Value = '
TxtJAM.Value = '
cboBANK.Value = '
txtNOBANK.Value = '
TxtBAYARDI.Value = '
txtKJ.Value = '
txtTGLP.Value = '
TxtPINJ.Value = '
optLAMA.Value = '
optBARU.Value = '
optDINAS.Value = '
optPENSIUNAN.Value = '
optUMUM.Value = '
optATM.Value = '
TxtKE.Value = '
txtHP.Value = '
Cbobayar.Value = '
TxtGaji.Value = '
JMGAJI.Value = '
MsgBox 'Data sudah disimpan', vbOKOnly
End Sub
Private Sub CMDCARI_Click()
On Error Resume Next
Dim Filter As String, Title As String, FileX As String
Dim Kode
Dim CellTujuan As Range
Kode = (cboRST.Value + TxtNOANG.Value)
Set CellTujuan = Worksheets('DATA').Range('B:B').Find(What:=Kode, LookIn:=xlValues, LookAt:=xlWhole)
If Not CellTujuan Is Nothing Then
txtNIK.Value = Worksheets('DATA').Cells(CellTujuan.Row, 3)
cboRST.Value = Worksheets('DATA').Cells(CellTujuan.Row, 4)
TxtNOANG.Value = Worksheets('DATA').Cells(CellTujuan.Row, 5)
TxtTGA.Value = Worksheets('DATA').Cells(CellTujuan.Row, 6)
txtNM.Value = Worksheets('DATA').Cells(CellTujuan.Row, 7)
txtTMP.Value = Worksheets('DATA').Cells(CellTujuan.Row, 8)
txtTGL.Value = Worksheets('DATA').Cells(CellTujuan.Row, 9)
txtJR.Value = Worksheets('DATA').Cells(CellTujuan.Row, 10)
txtKC.Value = Worksheets('DATA').Cells(CellTujuan.Row, 11)
TxtANG.Value = Worksheets('DATA').Cells(CellTujuan.Row, 12)
TxtJAM.Value = Worksheets('DATA').Cells(CellTujuan.Row, 13)
cboBANK.Value = Worksheets('DATA').Cells(CellTujuan.Row, 14)
txtNOBANK.Value = Worksheets('DATA').Cells(CellTujuan.Row, 15)
TxtBAYARDI.Value = Worksheets('DATA').Cells(CellTujuan.Row, 16)
txtKJ.Value = Worksheets('DATA').Cells(CellTujuan.Row, 17)
txtTGLP.Value = Worksheets('DATA').Cells(CellTujuan.Row, 18)
TxtPINJ.Value = Worksheets('DATA').Cells(CellTujuan.Row, 19)
If Worksheets('DATA').Cells(CellTujuan.Row, 20) = 'LAMA' Then
optLAMA.Value = True
ElseIf Worksheets('DATA').Cells(CellTujuan.Row, 20) = 'BARU' Then
optBARU.Value = True
End If
If Worksheets('DATA').Cells(CellTujuan.Row, 21) = 'DINAS' Then
optDINAS.Value = True
ElseIf Worksheets('DATA').Cells(CellTujuan.Row, 21) = 'PENSIUNAN' Then
optPENSIUNAN.Value = True
ElseIf Worksheets('DATA').Cells(CellTujuan.Row, 21) = 'UMUM' Then
optUMUM.Value = True
ElseIf Worksheets('DATA').Cells(CellTujuan.Row, 21) = 'ATM' Then
optATM.Value = True
End If
TxtKE.Value = Worksheets('DATA').Cells(CellTujuan.Row, 22)
txtHP.Value = Worksheets('DATA').Cells(CellTujuan.Row, 23)
Cbobayar.Value = Worksheets('DATA').Cells(CellTujuan.Row, 24)
TxtGaji.Value = Worksheets('DATA').Cells(CellTujuan.Row, 25)
JMGAJI.Value = Worksheets('DATA').Cells(CellTujuan.Row, 27)
Else: MsgBox 'Tidak Ada Hasil !'
End If
Application.ScreenUpdating = False
NamaFile = (cboRST.Value + TxtNOANG.Value + ' ' + txtNM.Value)
FileX = ActiveWorkbook.Path & 'Photo' & NamaFile & '.jpg'
FOTO.Picture = LoadPicture(FileX)
FOTO.Height = TRANSAKSI.Height - 405 '+ Image1.Height
FOTO.Width = TRANSAKSI.Width - 655 '+ Image1.Height
FOTO.Top = 36
FOTO.Left = 606
Application.ScreenUpdating = True
End SubUNTUK ANGGOTA LAMA
On Error Resume Next
Dim Filter As String, Title As String, FileX As String
Dim Kode
Dim CellTujuan As Range
Kode = (cboRST.Value + TxtNOANG.Value)
Set CellTujuan = Worksheets('DATA').Range('B:B').Find(What:=Kode, LookIn:=xlValues, LookAt:=xlWhole)
If Not CellTujuan Is Nothing Then
txtNIK.Value = Worksheets('DATA').Cells(CellTujuan.Row, 3)
cboRST.Value = Worksheets('DATA').Cells(CellTujuan.Row, 4)
TxtNOANG.Value = Worksheets('DATA').Cells(CellTujuan.Row, 5)
TxtTGA.Value = Worksheets('DATA').Cells(CellTujuan.Row, 6)
txtNM.Value = Worksheets('DATA').Cells(CellTujuan.Row, 7)
txtTMP.Value = Worksheets('DATA').Cells(CellTujuan.Row, 8)
txtTGL.Value = Worksheets('DATA').Cells(CellTujuan.Row, 9)
txtJR.Value = Worksheets('DATA').Cells(CellTujuan.Row, 10)
txtKC.Value = Worksheets('DATA').Cells(CellTujuan.Row, 11)
TxtANG.Value = Worksheets('DATA').Cells(CellTujuan.Row, 12)
TxtJAM.Value = Worksheets('DATA').Cells(CellTujuan.Row, 13)
cboBANK.Value = Worksheets('DATA').Cells(CellTujuan.Row, 14)
txtNOBANK.Value = Worksheets('DATA').Cells(CellTujuan.Row, 15)
TxtBAYARDI.Value = Worksheets('DATA').Cells(CellTujuan.Row, 16)
txtKJ.Value = Worksheets('DATA').Cells(CellTujuan.Row, 17)
txtTGLP.Value = Worksheets('DATA').Cells(CellTujuan.Row, 18)
TxtPINJ.Value = Worksheets('DATA').Cells(CellTujuan.Row, 19)
If Worksheets('DATA').Cells(CellTujuan.Row, 20) = 'LAMA' Then
optLAMA.Value = True
ElseIf Worksheets('DATA').Cells(CellTujuan.Row, 20) = 'BARU' Then
optBARU.Value = True
End If
If Worksheets('DATA').Cells(CellTujuan.Row, 21) = 'DINAS' Then
optDINAS.Value = True
ElseIf Worksheets('DATA').Cells(CellTujuan.Row, 21) = 'PENSIUNAN' Then
optPENSIUNAN.Value = True
ElseIf Worksheets('DATA').Cells(CellTujuan.Row, 21) = 'UMUM' Then
optUMUM.Value = True
ElseIf Worksheets('DATA').Cells(CellTujuan.Row, 21) = 'ATM' Then
optATM.Value = True
End If
TxtKE.Value = Worksheets('DATA').Cells(CellTujuan.Row, 22)
txtHP.Value = Worksheets('DATA').Cells(CellTujuan.Row, 23)
Cbobayar.Value = Worksheets('DATA').Cells(CellTujuan.Row, 24)
TxtGaji.Value = Worksheets('DATA').Cells(CellTujuan.Row, 25)
JMGAJI.Value = Worksheets('DATA').Cells(CellTujuan.Row, 27)
Else: MsgBox 'Tidak Ada Hasil !'
End If
Application.ScreenUpdating = False
NamaFile = (cboRST.Value + TxtNOANG.Value + ' ' + txtNM.Value)
FileX = ActiveWorkbook.Path & 'Photo' & NamaFile & '.jpg'
FOTO.Picture = LoadPicture(FileX)
FOTO.Height = TRANSAKSI.Height - 405 '+ Image1.Height
FOTO.Width = TRANSAKSI.Width - 655 '+ Image1.Height
FOTO.Top = 36
FOTO.Left = 606
Application.ScreenUpdating = True
End SubUNTUK ANGGOTA LAMA
Download Cara Membuat Laporan Koperasi Simpan Pinjam Dengan Excel 2017 Pdf
Private Sub ANGGOTALAMA_Click()
On Error Resume Next
Dim Filter As String, Title As String, FileX As String
Dim Kode
Dim TujuanData As Range
Kode = (cboRST.Text + TxtNOANG.Text)
Set TujuanData = Sheets('DATA').Range('B:B').Find(What:=Kode, LookIn:=xlValues, LookAt:=xlWhole)
If Not TujuanData Is Nothing Then
Worksheets('DATA').Cells(TujuanData.Row, 3).Value = txtNIK.Value
Worksheets('DATA').Cells(TujuanData.Row, 4).Value = cboRST.Value
Worksheets('DATA').Cells(TujuanData.Row, 5).Value = TxtNOANG.Value
Worksheets('DATA').Cells(TujuanData.Row, 6).Value = TxtTGA.Value
Worksheets('DATA').Cells(TujuanData.Row, 7).Value = txtNM.Value
Worksheets('DATA').Cells(TujuanData.Row, 8).Value = txtTMP.Value
Worksheets('DATA').Cells(TujuanData.Row, 9).Value = txtTGL.Value
Worksheets('DATA').Cells(TujuanData.Row, 10).Value = txtJR.Value
Worksheets('DATA').Cells(TujuanData.Row, 11).Value = txtKC.Value
Worksheets('DATA').Cells(TujuanData.Row, 12).Value = TxtANG.Value
Worksheets('DATA').Cells(TujuanData.Row, 13).Value = TxtJAM.Value
Worksheets('DATA').Cells(TujuanData.Row, 14).Value = cboBANK.Value
Worksheets('DATA').Cells(TujuanData.Row, 15).Value = txtNOBANK.Value
Worksheets('DATA').Cells(TujuanData.Row, 16).Value = TxtBAYARDI.Value
Worksheets('DATA').Cells(TujuanData.Row, 17).Value = txtKJ.Value
Worksheets('DATA').Cells(TujuanData.Row, 18).Value = txtTGLP.Value
Worksheets('DATA').Cells(TujuanData.Row, 19).Value = TxtPINJ.Value
If optLAMA = True Then
Worksheets('DATA').Cells(TujuanData.Row, 20).Value = 'LAMA'
ElseIf optBARU = True Then
Worksheets('DATA').Cells(TujuanData.Row, 20).Value = 'BARU'
End If
If optDINAS = True Then
Worksheets('DATA').Cells(TujuanData.Row, 21).Value = 'DINAS'
ElseIf optPENSIUNAN = True Then
Worksheets('DATA').Cells(TujuanData.Row, 21).Value = 'PENSIUNAN'
ElseIf optUMUM = True Then
Worksheets('DATA').Cells(TujuanData.Row, 21).Value = 'UMUM'
ElseIf optATM = True Then
Worksheets('DATA').Cells(TujuanData.Row, 21).Value = 'ATM'
End If
Worksheets('DATA').Cells(TujuanData.Row, 22).Value = TxtKE.Value
Worksheets('DATA').Cells(TujuanData.Row, 23).Value = txtHP.Value
Worksheets('DATA').Cells(TujuanData.Row, 24).Value = Cbobayar.Value
Worksheets('DATA').Cells(TujuanData.Row, 25).Value = TxtGaji.Value
Worksheets('DATA').Cells(TujuanData.Row, 26).Value = '=DATEDIF(RC[-17],NOW(),'y')&' '&'Tahun''
Worksheets('DATA').Cells(TujuanData.Row, 27).Value = JMGAJI.Value
End If
With Worksheets('BLANGKO')
Worksheets('BLANGKO').Cells(2, 1) = txtNM.Value
Worksheets('BLANGKO').Cells(2, 2) = txtTMP.Value
Worksheets('BLANGKO').Cells(2, 3) = TxtTGA.Value
Worksheets('BLANGKO').Cells(2, 4) = txtKJ.Value
Worksheets('BLANGKO').Cells(2, 5) = txtJR.Value
Worksheets('BLANGKO').Cells(2, 6) = txtKC.Value
Worksheets('BLANGKO').Cells(2, 7) = TxtPINJ.Value
Worksheets('BLANGKO').Cells(2, 8) = '=TERBILANG(RC[-1])'
Worksheets('BLANGKO').Cells(2, 9) = cboBANK.Value
Worksheets('BLANGKO').Cells(2, 10) = txtNOBANK.Value
Worksheets('BLANGKO').Cells(2, 11) = cboRST.Value
Worksheets('BLANGKO').Cells(2, 12) = TxtANG.Value
Worksheets('BLANGKO').Cells(2, 13) = TxtJAM.Value
Worksheets('BLANGKO').Cells(2, 14) = txtTGLP.Value
Worksheets('BLANGKO').Cells(2, 15) = (TxtPINJ.Value / 12)
Worksheets('BLANGKO').Cells(2, 16) = '=TERBILANG(RC[-1])'
Worksheets('BLANGKO').Cells(2, 17) = '=RC[-3]+30'
Worksheets('BLANGKO').Cells(2, 18) = TxtNOANG.Value
End With
With Worksheets('BERKAS')
Worksheets('BERKAS').Cells(3, 11) = TxtNOANG.Value
Worksheets('BERKAS').Cells(11, 11) = cboRST.Value
Worksheets('BERKAS').Cells(7, 2) = txtTGLP.Value
Worksheets('BERKAS').Cells(10, 5) = txtNM.Value
Worksheets('BERKAS').Cells(11, 3) = txtJR.Value
Worksheets('BERKAS').Cells(12, 3) = txtKC.Value
Worksheets('BERKAS').Cells(13, 5) = TxtANG.Value
Worksheets('BERKAS').Cells(14, 5) = TxtJAM.Value
Worksheets('BERKAS').Cells(15, 5) = (cboBANK.Value + txtNOBANK.Value)
End With
With Worksheets('KARTU')
Worksheets('KARTU').Cells(2, 3) = TxtPINJ.Value
Worksheets('KARTU').Cells(3, 1) = '=TERBILANG(R[-1]C[2])'
Worksheets('KARTU').Cells(7, 5) = txtNM.Value
Worksheets('KARTU').Cells(8, 5) = txtTMP.Value
Worksheets('KARTU').Cells(8, 6) = txtTGL.Value
Worksheets('KARTU').Cells(8, 7) = '=DATEDIF(RC[-1],NOW(),'y')&' '&'Tahun''
Worksheets('KARTU').Cells(9, 5) = txtJR.Value
Worksheets('KARTU').Cells(10, 5) = txtKC.Value
Worksheets('KARTU').Cells(12, 5) = cboRST.Value
Worksheets('KARTU').Cells(7, 10) = TxtNOANG.Value
Worksheets('KARTU').Cells(9, 10) = TxtBAYARDI.Value
Worksheets('KARTU').Cells(10, 10) = Cbobayar.Value
Worksheets('KARTU').Cells(12, 10) = TxtANG.Value
Worksheets('KARTU').Cells(13, 9) = TxtJAM.Value
Worksheets('KARTU').Cells(13, 7) = txtNOBANK.Value
Worksheets('KARTU').Cells(16, 2) = txtTGLP.Value
Worksheets('KARTU').Cells(16, 8) = TxtPINJ.Value
Worksheets('KARTU').Cells(11, 6) = txtKJ.Value
Application.ScreenUpdating = False
NamaFile = (cboRST.Value + TxtNOANG.Value + ' ' + txtNM.Value)
FileX = ActiveWorkbook.Path & 'Photo' & NamaFile & '.jpg'
Worksheets('KARTU').Image1.Picture = LoadPicture(FileX)
Application.ScreenUpdating = True
End With
With Worksheets('BALEK KARTU')
Worksheets('BALEK KARTU').Cells(9, 3) = JMGAJI.Value
Worksheets('BALEK KARTU').Cells(10, 2) = '=terbilang(R[-1]C[1])'
Worksheets('BALEK KARTU').Cells(18, 2) = txtNM.Value
Worksheets('BALEK KARTU').Cells(19, 3) = txtTGLP.Value
End With
With Worksheets('KWITANSI')
Worksheets('KWITANSI').Cells(2, 5) = cboRST.Value
Worksheets('KWITANSI').Cells(23, 5) = cboRST.Value
Worksheets('KWITANSI').Cells(2, 6) = TxtNOANG.Value
Worksheets('KWITANSI').Cells(23, 6) = TxtNOANG.Value
Worksheets('KWITANSI').Cells(12, 10) = txtTGLP.Value
Worksheets('KWITANSI').Cells(35, 10) = txtTGLP.Value
Worksheets('KWITANSI').Cells(18, 14) = txtNM.Value
Worksheets('KWITANSI').Cells(18, 14) = txtNM.Value
Worksheets('KWITANSI').Cells(6, 8) = '=TERBILANG(R[12]C[-3])'
Worksheets('KWITANSI').Cells(27, 8) = '=TERBILANG(R[12]C[-3])'
Worksheets('KWITANSI').Cells(18, 5) = TxtPINJ.Value
Worksheets('KWITANSI').Cells(39, 5) = TxtPINJ.Value
End With
Select Case cboRST.Value
Case 'ANGGREK'
Dim CellANGGREK As Long
With Worksheets('ANGGREK')
CellANGGREK = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'--- data input
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 1).Value = CellANGGREK - 1
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 2).Value = txtTGLP.Value
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 3).Value = (Day(txtTGLP.Value))
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 4).Value = TxtNOANG.Value
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 5).Value = txtNM.Value
If optDINAS = True Then
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 6).Value = 1
ElseIf optPENSIUNAN = True Then
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 7).Value = 1
ElseIf optUMUM = True Then
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 8).Value = 1
ElseIf optATM = True Then
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 8).Value = 1
End If
If optLAMA = True Then
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 10).Value = 1
ElseIf optBARU = True Then
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 11).Value = 1
End If
If optLAMA = True Then
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 12).Value = TxtPINJ.Value
ElseIf optBARU = True Then
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 13).Value = TxtPINJ.Value
End If
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 14).Value = (TxtPINJ.Value * 3 / 100)
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 15).Value = '=IF(RC[-4]=1,5000,'')'
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 16).Value = '=SUM(RC[-4]:RC[-3])-SUM(RC[-2]:RC[-1])'
End With
Case 'MAWAR'
Dim CellMAWAR As Long
With Worksheets('MAWAR')
CellMAWAR = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'--- data input
Worksheets('MAWAR').Cells(CellMAWAR + 1, 1).Value = CellMAWAR - 1
Worksheets('MAWAR').Cells(CellMAWAR + 1, 2).Value = txtTGLP.Value
Worksheets('MAWAR').Cells(CellMAWAR + 1, 3).Value = (Day(txtTGLP.Value))
Worksheets('MAWAR').Cells(CellMAWAR + 1, 4).Value = TxtNOANG.Value
Worksheets('MAWAR').Cells(CellMAWAR + 1, 5).Value = txtNM.Value
If optDINAS = True Then
Worksheets('MAWAR').Cells(CellMAWAR + 1, 6).Value = 1
ElseIf optPENSIUNAN = True Then
Worksheets('MAWAR').Cells(CellMAWAR + 1, 7).Value = 1
ElseIf optUMUM = True Then
Worksheets('MAWAR').Cells(CellMAWAR + 1, 8).Value = 1
ElseIf optATM = True Then
Worksheets('MAWAR').Cells(CellMAWAR + 1, 8).Value = 1
End If
If optLAMA = True Then
Worksheets('MAWAR').Cells(CellMAWAR + 1, 10).Value = 1
ElseIf optBARU = True Then
Worksheets('MAWAR').Cells(CellMAWAR + 1, 11).Value = 1
End If
If optLAMA = True Then
Worksheets('MAWAR').Cells(CellMAWAR + 1, 12).Value = TxtPINJ.Value
ElseIf optBARU = True Then
Worksheets('MAWAR').Cells(CellMAWAR + 1, 13).Value = TxtPINJ.Value
End If
Worksheets('MAWAR').Cells(CellMAWAR + 1, 14).Value = (TxtPINJ.Value * 3 / 100)
Worksheets('MAWAR').Cells(CellMAWAR + 1, 15).Value = '=IF(RC[-4]=1,5000,'')'
Worksheets('MAWAR').Cells(CellMAWAR + 1, 16).Value = '=SUM(RC[-4]:RC[-3])-SUM(RC[-2]:RC[-1])'
End With
Case ('TERATAI')
Dim CellTERATAI As Long
With Worksheets('TERATAI')
CellTERATAI = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'--- data input
Worksheets('TERATAI').Cells(CellTERATAI + 1, 1).Value = CellTERATAI - 1
Worksheets('TERATAI').Cells(CellTERATAI + 1, 2).Value = txtTGLP.Value
Worksheets('TERATAI').Cells(CellTERATAI + 1, 3).Value = (Day(txtTGLP.Value))
Worksheets('TERATAI').Cells(CellTERATAI + 1, 4).Value = TxtNOANG.Value
Worksheets('TERATAI').Cells(CellTERATAI + 1, 5).Value = txtNM.Value
If optDINAS = True Then
Worksheets('TERATAI').Cells(CellTERATAI + 1, 6).Value = 1
ElseIf optPENSIUNAN = True Then
Worksheets('TERATAI').Cells(CellTERATAI + 1, 7).Value = 1
ElseIf optUMUM = True Then
Worksheets('TERATAI').Cells(CellTERATAI + 1, 8).Value = 1
ElseIf optATM = True Then
Worksheets('TERATAI').Cells(CellTERATAI + 1, 8).Value = 1
End If
If optLAMA = True Then
Worksheets('TERATAI').Cells(CellTERATAI + 1, 10).Value = 1
ElseIf optBARU = True Then
Worksheets('TERATAI').Cells(CellTERATAI + 1, 11).Value = 1
End If
If optLAMA = True Then
Worksheets('TERATAI').Cells(CellTERATAI + 1, 12).Value = TxtPINJ.Value
ElseIf optBARU = True Then
Worksheets('TERATAI').Cells(CellTERATAI + 1, 13).Value = TxtPINJ.Value
End If
Worksheets('TERATAI').Cells(CellTERATAI + 1, 14).Value = (TxtPINJ.Value * 3 / 100)
Worksheets('TERATAI').Cells(CellTERATAI + 1, 15).Value = '=IF(RC[-4]=1,5000,'')'
Worksheets('TERATAI').Cells(CellTERATAI + 1, 16).Value = '=SUM(RC[-4]:RC[-3])-SUM(RC[-2]:RC[-1])'
End With
End Select
txtNIK.Value = '
cboRST.Value = '
TxtNOANG.Value = '
TxtTGA.Value = '
txtNM.Value = '
txtTMP.Value = '
txtTGL.Value = '
txtJR.Value = '
txtKC.Value = '
TxtANG.Value = '
TxtJAM.Value = '
cboBANK.Value = '
txtNOBANK.Value = '
TxtBAYARDI.Value = '
txtKJ.Value = '
txtTGLP.Value = '
TxtPINJ.Value = '
optLAMA.Value = '
optBARU.Value = '
optDINAS.Value = '
optPENSIUNAN.Value = '
optUMUM.Value = '
optATM.Value = '
TxtKE.Value = '
txtHP.Value = '
Cbobayar.Value = '
TxtGaji.Value = '
JMGAJI.Value = '
MsgBox 'Data sudah disimpan', vbOKOnly
End Sub
UNTUK INSERT FOTO ANGGOTA
On Error Resume Next
Dim Filter As String, Title As String, FileX As String
Dim Kode
Dim TujuanData As Range
Kode = (cboRST.Text + TxtNOANG.Text)
Set TujuanData = Sheets('DATA').Range('B:B').Find(What:=Kode, LookIn:=xlValues, LookAt:=xlWhole)
If Not TujuanData Is Nothing Then
Worksheets('DATA').Cells(TujuanData.Row, 3).Value = txtNIK.Value
Worksheets('DATA').Cells(TujuanData.Row, 4).Value = cboRST.Value
Worksheets('DATA').Cells(TujuanData.Row, 5).Value = TxtNOANG.Value
Worksheets('DATA').Cells(TujuanData.Row, 6).Value = TxtTGA.Value
Worksheets('DATA').Cells(TujuanData.Row, 7).Value = txtNM.Value
Worksheets('DATA').Cells(TujuanData.Row, 8).Value = txtTMP.Value
Worksheets('DATA').Cells(TujuanData.Row, 9).Value = txtTGL.Value
Worksheets('DATA').Cells(TujuanData.Row, 10).Value = txtJR.Value
Worksheets('DATA').Cells(TujuanData.Row, 11).Value = txtKC.Value
Worksheets('DATA').Cells(TujuanData.Row, 12).Value = TxtANG.Value
Worksheets('DATA').Cells(TujuanData.Row, 13).Value = TxtJAM.Value
Worksheets('DATA').Cells(TujuanData.Row, 14).Value = cboBANK.Value
Worksheets('DATA').Cells(TujuanData.Row, 15).Value = txtNOBANK.Value
Worksheets('DATA').Cells(TujuanData.Row, 16).Value = TxtBAYARDI.Value
Worksheets('DATA').Cells(TujuanData.Row, 17).Value = txtKJ.Value
Worksheets('DATA').Cells(TujuanData.Row, 18).Value = txtTGLP.Value
Worksheets('DATA').Cells(TujuanData.Row, 19).Value = TxtPINJ.Value
If optLAMA = True Then
Worksheets('DATA').Cells(TujuanData.Row, 20).Value = 'LAMA'
ElseIf optBARU = True Then
Worksheets('DATA').Cells(TujuanData.Row, 20).Value = 'BARU'
End If
If optDINAS = True Then
Worksheets('DATA').Cells(TujuanData.Row, 21).Value = 'DINAS'
ElseIf optPENSIUNAN = True Then
Worksheets('DATA').Cells(TujuanData.Row, 21).Value = 'PENSIUNAN'
ElseIf optUMUM = True Then
Worksheets('DATA').Cells(TujuanData.Row, 21).Value = 'UMUM'
ElseIf optATM = True Then
Worksheets('DATA').Cells(TujuanData.Row, 21).Value = 'ATM'
End If
Worksheets('DATA').Cells(TujuanData.Row, 22).Value = TxtKE.Value
Worksheets('DATA').Cells(TujuanData.Row, 23).Value = txtHP.Value
Worksheets('DATA').Cells(TujuanData.Row, 24).Value = Cbobayar.Value
Worksheets('DATA').Cells(TujuanData.Row, 25).Value = TxtGaji.Value
Worksheets('DATA').Cells(TujuanData.Row, 26).Value = '=DATEDIF(RC[-17],NOW(),'y')&' '&'Tahun''
Worksheets('DATA').Cells(TujuanData.Row, 27).Value = JMGAJI.Value
End If
With Worksheets('BLANGKO')
Worksheets('BLANGKO').Cells(2, 1) = txtNM.Value
Worksheets('BLANGKO').Cells(2, 2) = txtTMP.Value
Worksheets('BLANGKO').Cells(2, 3) = TxtTGA.Value
Worksheets('BLANGKO').Cells(2, 4) = txtKJ.Value
Worksheets('BLANGKO').Cells(2, 5) = txtJR.Value
Worksheets('BLANGKO').Cells(2, 6) = txtKC.Value
Worksheets('BLANGKO').Cells(2, 7) = TxtPINJ.Value
Worksheets('BLANGKO').Cells(2, 8) = '=TERBILANG(RC[-1])'
Worksheets('BLANGKO').Cells(2, 9) = cboBANK.Value
Worksheets('BLANGKO').Cells(2, 10) = txtNOBANK.Value
Worksheets('BLANGKO').Cells(2, 11) = cboRST.Value
Worksheets('BLANGKO').Cells(2, 12) = TxtANG.Value
Worksheets('BLANGKO').Cells(2, 13) = TxtJAM.Value
Worksheets('BLANGKO').Cells(2, 14) = txtTGLP.Value
Worksheets('BLANGKO').Cells(2, 15) = (TxtPINJ.Value / 12)
Worksheets('BLANGKO').Cells(2, 16) = '=TERBILANG(RC[-1])'
Worksheets('BLANGKO').Cells(2, 17) = '=RC[-3]+30'
Worksheets('BLANGKO').Cells(2, 18) = TxtNOANG.Value
End With
With Worksheets('BERKAS')
Worksheets('BERKAS').Cells(3, 11) = TxtNOANG.Value
Worksheets('BERKAS').Cells(11, 11) = cboRST.Value
Worksheets('BERKAS').Cells(7, 2) = txtTGLP.Value
Worksheets('BERKAS').Cells(10, 5) = txtNM.Value
Worksheets('BERKAS').Cells(11, 3) = txtJR.Value
Worksheets('BERKAS').Cells(12, 3) = txtKC.Value
Worksheets('BERKAS').Cells(13, 5) = TxtANG.Value
Worksheets('BERKAS').Cells(14, 5) = TxtJAM.Value
Worksheets('BERKAS').Cells(15, 5) = (cboBANK.Value + txtNOBANK.Value)
End With
With Worksheets('KARTU')
Worksheets('KARTU').Cells(2, 3) = TxtPINJ.Value
Worksheets('KARTU').Cells(3, 1) = '=TERBILANG(R[-1]C[2])'
Worksheets('KARTU').Cells(7, 5) = txtNM.Value
Worksheets('KARTU').Cells(8, 5) = txtTMP.Value
Worksheets('KARTU').Cells(8, 6) = txtTGL.Value
Worksheets('KARTU').Cells(8, 7) = '=DATEDIF(RC[-1],NOW(),'y')&' '&'Tahun''
Worksheets('KARTU').Cells(9, 5) = txtJR.Value
Worksheets('KARTU').Cells(10, 5) = txtKC.Value
Worksheets('KARTU').Cells(12, 5) = cboRST.Value
Worksheets('KARTU').Cells(7, 10) = TxtNOANG.Value
Worksheets('KARTU').Cells(9, 10) = TxtBAYARDI.Value
Worksheets('KARTU').Cells(10, 10) = Cbobayar.Value
Worksheets('KARTU').Cells(12, 10) = TxtANG.Value
Worksheets('KARTU').Cells(13, 9) = TxtJAM.Value
Worksheets('KARTU').Cells(13, 7) = txtNOBANK.Value
Worksheets('KARTU').Cells(16, 2) = txtTGLP.Value
Worksheets('KARTU').Cells(16, 8) = TxtPINJ.Value
Worksheets('KARTU').Cells(11, 6) = txtKJ.Value
Application.ScreenUpdating = False
NamaFile = (cboRST.Value + TxtNOANG.Value + ' ' + txtNM.Value)
FileX = ActiveWorkbook.Path & 'Photo' & NamaFile & '.jpg'
Worksheets('KARTU').Image1.Picture = LoadPicture(FileX)
Application.ScreenUpdating = True
End With
With Worksheets('BALEK KARTU')
Worksheets('BALEK KARTU').Cells(9, 3) = JMGAJI.Value
Worksheets('BALEK KARTU').Cells(10, 2) = '=terbilang(R[-1]C[1])'
Worksheets('BALEK KARTU').Cells(18, 2) = txtNM.Value
Worksheets('BALEK KARTU').Cells(19, 3) = txtTGLP.Value
End With
With Worksheets('KWITANSI')
Worksheets('KWITANSI').Cells(2, 5) = cboRST.Value
Worksheets('KWITANSI').Cells(23, 5) = cboRST.Value
Worksheets('KWITANSI').Cells(2, 6) = TxtNOANG.Value
Worksheets('KWITANSI').Cells(23, 6) = TxtNOANG.Value
Worksheets('KWITANSI').Cells(12, 10) = txtTGLP.Value
Worksheets('KWITANSI').Cells(35, 10) = txtTGLP.Value
Worksheets('KWITANSI').Cells(18, 14) = txtNM.Value
Worksheets('KWITANSI').Cells(18, 14) = txtNM.Value
Worksheets('KWITANSI').Cells(6, 8) = '=TERBILANG(R[12]C[-3])'
Worksheets('KWITANSI').Cells(27, 8) = '=TERBILANG(R[12]C[-3])'
Worksheets('KWITANSI').Cells(18, 5) = TxtPINJ.Value
Worksheets('KWITANSI').Cells(39, 5) = TxtPINJ.Value
End With
Select Case cboRST.Value
Case 'ANGGREK'
Dim CellANGGREK As Long
With Worksheets('ANGGREK')
CellANGGREK = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'--- data input
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 1).Value = CellANGGREK - 1
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 2).Value = txtTGLP.Value
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 3).Value = (Day(txtTGLP.Value))
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 4).Value = TxtNOANG.Value
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 5).Value = txtNM.Value
If optDINAS = True Then
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 6).Value = 1
ElseIf optPENSIUNAN = True Then
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 7).Value = 1
ElseIf optUMUM = True Then
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 8).Value = 1
ElseIf optATM = True Then
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 8).Value = 1
End If
If optLAMA = True Then
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 10).Value = 1
ElseIf optBARU = True Then
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 11).Value = 1
End If
If optLAMA = True Then
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 12).Value = TxtPINJ.Value
ElseIf optBARU = True Then
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 13).Value = TxtPINJ.Value
End If
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 14).Value = (TxtPINJ.Value * 3 / 100)
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 15).Value = '=IF(RC[-4]=1,5000,'')'
Worksheets('ANGGREK').Cells(CellANGGREK + 1, 16).Value = '=SUM(RC[-4]:RC[-3])-SUM(RC[-2]:RC[-1])'
End With
Case 'MAWAR'
Dim CellMAWAR As Long
With Worksheets('MAWAR')
CellMAWAR = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'--- data input
Worksheets('MAWAR').Cells(CellMAWAR + 1, 1).Value = CellMAWAR - 1
Worksheets('MAWAR').Cells(CellMAWAR + 1, 2).Value = txtTGLP.Value
Worksheets('MAWAR').Cells(CellMAWAR + 1, 3).Value = (Day(txtTGLP.Value))
Worksheets('MAWAR').Cells(CellMAWAR + 1, 4).Value = TxtNOANG.Value
Worksheets('MAWAR').Cells(CellMAWAR + 1, 5).Value = txtNM.Value
If optDINAS = True Then
Worksheets('MAWAR').Cells(CellMAWAR + 1, 6).Value = 1
ElseIf optPENSIUNAN = True Then
Worksheets('MAWAR').Cells(CellMAWAR + 1, 7).Value = 1
ElseIf optUMUM = True Then
Worksheets('MAWAR').Cells(CellMAWAR + 1, 8).Value = 1
ElseIf optATM = True Then
Worksheets('MAWAR').Cells(CellMAWAR + 1, 8).Value = 1
End If
If optLAMA = True Then
Worksheets('MAWAR').Cells(CellMAWAR + 1, 10).Value = 1
ElseIf optBARU = True Then
Worksheets('MAWAR').Cells(CellMAWAR + 1, 11).Value = 1
End If
If optLAMA = True Then
Worksheets('MAWAR').Cells(CellMAWAR + 1, 12).Value = TxtPINJ.Value
ElseIf optBARU = True Then
Worksheets('MAWAR').Cells(CellMAWAR + 1, 13).Value = TxtPINJ.Value
End If
Worksheets('MAWAR').Cells(CellMAWAR + 1, 14).Value = (TxtPINJ.Value * 3 / 100)
Worksheets('MAWAR').Cells(CellMAWAR + 1, 15).Value = '=IF(RC[-4]=1,5000,'')'
Worksheets('MAWAR').Cells(CellMAWAR + 1, 16).Value = '=SUM(RC[-4]:RC[-3])-SUM(RC[-2]:RC[-1])'
End With
Case ('TERATAI')
Dim CellTERATAI As Long
With Worksheets('TERATAI')
CellTERATAI = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'--- data input
Worksheets('TERATAI').Cells(CellTERATAI + 1, 1).Value = CellTERATAI - 1
Worksheets('TERATAI').Cells(CellTERATAI + 1, 2).Value = txtTGLP.Value
Worksheets('TERATAI').Cells(CellTERATAI + 1, 3).Value = (Day(txtTGLP.Value))
Worksheets('TERATAI').Cells(CellTERATAI + 1, 4).Value = TxtNOANG.Value
Worksheets('TERATAI').Cells(CellTERATAI + 1, 5).Value = txtNM.Value
If optDINAS = True Then
Worksheets('TERATAI').Cells(CellTERATAI + 1, 6).Value = 1
ElseIf optPENSIUNAN = True Then
Worksheets('TERATAI').Cells(CellTERATAI + 1, 7).Value = 1
ElseIf optUMUM = True Then
Worksheets('TERATAI').Cells(CellTERATAI + 1, 8).Value = 1
ElseIf optATM = True Then
Worksheets('TERATAI').Cells(CellTERATAI + 1, 8).Value = 1
End If
If optLAMA = True Then
Worksheets('TERATAI').Cells(CellTERATAI + 1, 10).Value = 1
ElseIf optBARU = True Then
Worksheets('TERATAI').Cells(CellTERATAI + 1, 11).Value = 1
End If
If optLAMA = True Then
Worksheets('TERATAI').Cells(CellTERATAI + 1, 12).Value = TxtPINJ.Value
ElseIf optBARU = True Then
Worksheets('TERATAI').Cells(CellTERATAI + 1, 13).Value = TxtPINJ.Value
End If
Worksheets('TERATAI').Cells(CellTERATAI + 1, 14).Value = (TxtPINJ.Value * 3 / 100)
Worksheets('TERATAI').Cells(CellTERATAI + 1, 15).Value = '=IF(RC[-4]=1,5000,'')'
Worksheets('TERATAI').Cells(CellTERATAI + 1, 16).Value = '=SUM(RC[-4]:RC[-3])-SUM(RC[-2]:RC[-1])'
End With
End Select
txtNIK.Value = '
cboRST.Value = '
TxtNOANG.Value = '
TxtTGA.Value = '
txtNM.Value = '
txtTMP.Value = '
txtTGL.Value = '
txtJR.Value = '
txtKC.Value = '
TxtANG.Value = '
TxtJAM.Value = '
cboBANK.Value = '
txtNOBANK.Value = '
TxtBAYARDI.Value = '
txtKJ.Value = '
txtTGLP.Value = '
TxtPINJ.Value = '
optLAMA.Value = '
optBARU.Value = '
optDINAS.Value = '
optPENSIUNAN.Value = '
optUMUM.Value = '
optATM.Value = '
TxtKE.Value = '
txtHP.Value = '
Cbobayar.Value = '
TxtGaji.Value = '
JMGAJI.Value = '
MsgBox 'Data sudah disimpan', vbOKOnly
End Sub
UNTUK INSERT FOTO ANGGOTA
Private Sub cmdnamafoto_Click()
On Error Resume Next
Dim Filter As String, Title As String, FileX As String
Dim SourceFile, DestinationFile
x.SetFocus
Filter = 'JPG Image Files Only(*.jpg),*.jpg,'
Title = 'Silahkan Pilih Logo'
FileX = Application.GetOpenFilename(Filter, , Title)
NamaFile = (cboRST.Value + TxtNOANG.Value + ' ' + txtNM.Value)
FOTO.Picture = LoadPicture(FileX)
FOTO.Height = TRANSAKSI.Height - 405 '+ Image1.Height
FOTO.Width = TRANSAKSI.Width - 655 '+ Image1.Height
FOTO.Top = 36
FOTO.Left = 606
DestinationFile = ActiveWorkbook.Path & 'Photo' & NamaFile & '.jpg'
FileCopy FileX, DestinationFile
End Sub
On Error Resume Next
Dim Filter As String, Title As String, FileX As String
Dim SourceFile, DestinationFile
x.SetFocus
Filter = 'JPG Image Files Only(*.jpg),*.jpg,'
Title = 'Silahkan Pilih Logo'
FileX = Application.GetOpenFilename(Filter, , Title)
NamaFile = (cboRST.Value + TxtNOANG.Value + ' ' + txtNM.Value)
FOTO.Picture = LoadPicture(FileX)
FOTO.Height = TRANSAKSI.Height - 405 '+ Image1.Height
FOTO.Width = TRANSAKSI.Width - 655 '+ Image1.Height
FOTO.Top = 36
FOTO.Left = 606
DestinationFile = ActiveWorkbook.Path & 'Photo' & NamaFile & '.jpg'
FileCopy FileX, DestinationFile
End Sub
Download Cara Membuat Laporan Koperasi Simpan Pinjam Dengan Excel 2017 Tutorial
UNTUK CONTOHNYA BISA DI DOWNLOAD DI BAWAH INIDownload Cara Membuat Laporan Koperasi Simpan Pinjam Dengan Excel 2017 Free
DOWNLOADComents are closed