Cara Membuat Input Nilai Otomatis microsoft excel vba

Ada beberapa cara untuk menginput nilai hasil evaluasi pembelajaran salah satunya memanfaatkan visual basic (Vba)  yang terdapat pada microsoft excel. Ketika kita ingin membuat aplikasi imputan nilai dengan membuat tombol (shape) input data otomatis. Berikut ini adalah scrip vba untuk membuat button input nilai pad microsoft excel.

Sub insertData()
Dim jumlahData As Integer
Dim nomor1, nis1, nisn1, nama1, sex1, spirt1, sos1, pnr1, pnk1, pp1, knr1, knk1, kp1 As String
Sheets("Sheet2").Select
nomor1 = Range("A12").Text
nis1 = Range("B12").Text
nisn1 = Range("C12").Text
nama1 = Range("D12").Text
sex1 = Range("E12").Text
spirt1 = Range("F12").Text
sos1 = Range("G12").Text
pnr1 = Range("H12").Text
pnk1 = Range("I12").Text
pp1 = Range("J12").Text
knr1 = Range("K12").Text
knk1 = Range("L12").Text
kp1 = Range("M12").Text 'batas ambil data row 1

Dim nomor2, nis2, nisn2, nama2, sex2, spirt2, sos2, pnr2, pnk2, pp2, knr2, knk2, kp2 As String
nomor2 = Range("A13").Text
nis2 = Range("B13").Text
nisn2 = Range("C13").Text
nama2 = Range("D13").Text
sex2 = Range("E13").Text
spirt2 = Range("F13").Text
sos2 = Range("G13").Text
pnr2 = Range("H13").Text
pnk2 = Range("I13").Text
pp2 = Range("J13").Text
knr2 = Range("K13").Text
knk2 = Range("L13").Text
kp2 = Range("M13").Text 'batas ambil data row 2

Dim nomor3, nis3, nisn3, nama3, sex3, spirt3, sos3, pnr3, pnk3, pp3, knr3, knk3, kp3 As String
nomor3 = Range("A14").Text
nis3 = Range("B14").Text
nisn3 = Range("C14").Text
nama3 = Range("D14").Text
sex3 = Range("E14").Text
spirt3 = Range("F14").Text
sos3 = Range("G14").Text
pnr3 = Range("H14").Text
pnk3 = Range("I14").Text
pp3 = Range("J14").Text
knr3 = Range("K14").Text
knk3 = Range("L14").Text
kp3 = Range("M14").Text 'batas ambil data row 3

Dim nomor4, nis4, nisn4, nama4, sex4, spirt4, sos4, pnr4, pnk4, pp4, knr4, knk4, kp4 As String
nomor4 = Range("A15").Text
nis4 = Range("B15").Text
nisn4 = Range("C15").Text
nama4 = Range("D15").Text
sex4 = Range("E15").Text
spirt4 = Range("F15").Text
sos4 = Range("G15").Text
pnr4 = Range("H15").Text
pnk4 = Range("I15").Text
pp4 = Range("J15").Text
knr4 = Range("K15").Text
knk4 = Range("L15").Text
kp4 = Range("M15").Text 'batas ambil data row 4

Dim nomor5, nis5, nisn5, nama5, sex5, spirt5, sos5, pnr5, pnk5, pp5, knr5, knk5, kp5 As String
nomor5 = Range("A16").Text
nis5 = Range("B16").Text
nisn5 = Range("C16").Text
nama5 = Range("D16").Text
sex5 = Range("E16").Text
spirt5 = Range("F16").Text
sos5 = Range("G16").Text
pnr5 = Range("H16").Text
pnk5 = Range("I16").Text
pp5 = Range("J16").Text
knr5 = Range("K16").Text
knk5 = Range("L16").Text
kp5 = Range("M16").Text 'batas ambil data row 5

Dim nomor6, nis6, nisn6, nama6, sex6, spirt6, sos6, pnr6, pnk6, pp6, knr6, knk6, kp6 As String
nomor6 = Range("A17").Text
nis6 = Range("B17").Text
nisn6 = Range("C17").Text
nama6 = Range("D17").Text
sex6 = Range("E17").Text
spirt6 = Range("F17").Text
sos6 = Range("G17").Text
pnr6 = Range("H17").Text
pnk6 = Range("I17").Text
pp6 = Range("J17").Text
knr6 = Range("K17").Text
knk6 = Range("L17").Text
kp6 = Range("M17").Text 'batas ambil data row 6

    Sheets("Sheet11").Select
    jumlahData = Range("N1").Value
    Rows(jumlahData + 2 & ":" & jumlahData + 2).Select
    Selection.Copy
    Rows(jumlahData + 2 & ":" & jumlahData + 2).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

    Range("A" & jumlahData + 3).Select 'input nomor1
    ActiveCell.FormulaR1C1 = jumlahData + 1
    Range("B" & jumlahData + 3).Select 'input nis1
    ActiveCell.FormulaR1C1 = nis1
    Range("C" & jumlahData + 3).Select 'input nisn1
    ActiveCell.FormulaR1C1 = nisn1
    Range("D" & jumlahData + 3).Select 'input nama1
    ActiveCell.FormulaR1C1 = nama1
    Range("E" & jumlahData + 3).Select 'input sex1
    ActiveCell.FormulaR1C1 = sex1
    Range("F" & jumlahData + 3).Select 'input spirt1
    ActiveCell.FormulaR1C1 = spirt1
    Range("G" & jumlahData + 3).Select 'input sos1
    ActiveCell.FormulaR1C1 = sos1
    Range("H" & jumlahData + 3).Select 'input pnr1
    ActiveCell.FormulaR1C1 = pnr1
    Range("I" & jumlahData + 3).Select 'input pnk1
    ActiveCell.FormulaR1C1 = pnk1
    Range("J" & jumlahData + 3).Select 'input pp1
    ActiveCell.FormulaR1C1 = pp1
    Range("K" & jumlahData + 3).Select 'input knr1
    ActiveCell.FormulaR1C1 = knr1
    Range("L" & jumlahData + 3).Select 'input knk1
    ActiveCell.FormulaR1C1 = knk1
    Range("M" & jumlahData + 3).Select 'input kp1
    ActiveCell.FormulaR1C1 = kp1
   
    Sheets("Sheet11").Select
    jumlahData = Range("N1").Value
    Rows(jumlahData + 2 & ":" & jumlahData + 2).Select
    Selection.Copy
    Rows(jumlahData + 2 & ":" & jumlahData + 2).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A" & jumlahData + 3).Select 'input nomor2
    ActiveCell.FormulaR1C1 = jumlahData + 1
    Range("B" & jumlahData + 3).Select 'input nis2
    ActiveCell.FormulaR1C1 = nis2
    Range("C" & jumlahData + 3).Select 'input nisn2
    ActiveCell.FormulaR1C1 = nisn2
    Range("D" & jumlahData + 3).Select 'input nama2
    ActiveCell.FormulaR1C1 = nama2
    Range("E" & jumlahData + 3).Select 'input sex2
    ActiveCell.FormulaR1C1 = sex2
    Range("F" & jumlahData + 3).Select 'input spirt2
    ActiveCell.FormulaR1C1 = spirt2
    Range("G" & jumlahData + 3).Select 'input sos2
    ActiveCell.FormulaR1C1 = sos2
    Range("H" & jumlahData + 3).Select 'input pnr2
    ActiveCell.FormulaR1C1 = pnr2
    Range("I" & jumlahData + 3).Select 'input pnk2
    ActiveCell.FormulaR1C1 = pnk2
    Range("J" & jumlahData + 3).Select 'input pp2
    ActiveCell.FormulaR1C1 = pp2
    Range("K" & jumlahData + 3).Select 'input knr2
    ActiveCell.FormulaR1C1 = knr2
    Range("L" & jumlahData + 3).Select 'input knk2
    ActiveCell.FormulaR1C1 = knk2
    Range("M" & jumlahData + 3).Select 'input kp2
    ActiveCell.FormulaR1C1 = kp2
   
    Sheets("Sheet11").Select
    jumlahData = Range("N1").Value
    Rows(jumlahData + 2 & ":" & jumlahData + 2).Select
    Selection.Copy
    Rows(jumlahData + 2 & ":" & jumlahData + 2).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A" & jumlahData + 3).Select 'input nomor3
    ActiveCell.FormulaR1C1 = jumlahData + 1
    Range("B" & jumlahData + 3).Select 'input nis3
    ActiveCell.FormulaR1C1 = nis3
    Range("C" & jumlahData + 3).Select 'input nisn3
    ActiveCell.FormulaR1C1 = nisn3
    Range("D" & jumlahData + 3).Select 'input nama3
    ActiveCell.FormulaR1C1 = nama3
    Range("E" & jumlahData + 3).Select 'input sex3
    ActiveCell.FormulaR1C1 = sex3
    Range("F" & jumlahData + 3).Select 'input spirt3
    ActiveCell.FormulaR1C1 = spirt3
    Range("G" & jumlahData + 3).Select 'input sos3
    ActiveCell.FormulaR1C1 = sos3
    Range("H" & jumlahData + 3).Select 'input pnr3
    ActiveCell.FormulaR1C1 = pnr2
    Range("I" & jumlahData + 3).Select 'input pnk2
    ActiveCell.FormulaR1C1 = pnk3
    Range("J" & jumlahData + 3).Select 'input pp3
    ActiveCell.FormulaR1C1 = pp3
    Range("K" & jumlahData + 3).Select 'input knr3
    ActiveCell.FormulaR1C1 = knr3
    Range("L" & jumlahData + 3).Select 'input knk3
    ActiveCell.FormulaR1C1 = knk3
    Range("M" & jumlahData + 3).Select 'input kp3
    ActiveCell.FormulaR1C1 = kp3
   
Sheets("Sheet11").Select
    jumlahData = Range("N1").Value
    Rows(jumlahData + 2 & ":" & jumlahData + 2).Select
    Selection.Copy
    Rows(jumlahData + 2 & ":" & jumlahData + 2).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A" & jumlahData + 3).Select 'input nomor4
    ActiveCell.FormulaR1C1 = jumlahData + 1
    Range("B" & jumlahData + 3).Select 'input nis4
    ActiveCell.FormulaR1C1 = nis4
    Range("C" & jumlahData + 3).Select 'input nisn4
    ActiveCell.FormulaR1C1 = nisn4
    Range("D" & jumlahData + 3).Select 'input nama4
    ActiveCell.FormulaR1C1 = nama4
    Range("E" & jumlahData + 3).Select 'input sex4
    ActiveCell.FormulaR1C1 = sex4
    Range("F" & jumlahData + 3).Select 'input spirt4
    ActiveCell.FormulaR1C1 = spirt4
    Range("G" & jumlahData + 3).Select 'input sos4
    ActiveCell.FormulaR1C1 = sos4
    Range("H" & jumlahData + 3).Select 'input pnr4
    ActiveCell.FormulaR1C1 = pnr4
    Range("I" & jumlahData + 3).Select 'input pnk4
    ActiveCell.FormulaR1C1 = pnk4
    Range("J" & jumlahData + 3).Select 'input pp4
    ActiveCell.FormulaR1C1 = pp4
    Range("K" & jumlahData + 3).Select 'input knr4
    ActiveCell.FormulaR1C1 = knr4
    Range("L" & jumlahData + 3).Select 'input knk4
    ActiveCell.FormulaR1C1 = knk4
    Range("M" & jumlahData + 3).Select 'input kp4
    ActiveCell.FormulaR1C1 = kp4
   
    Sheets("Sheet11").Select
    jumlahData = Range("N1").Value
    Rows(jumlahData + 2 & ":" & jumlahData + 2).Select
    Selection.Copy
    Rows(jumlahData + 2 & ":" & jumlahData + 2).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A" & jumlahData + 3).Select 'input nomor5
    ActiveCell.FormulaR1C1 = jumlahData + 1
    Range("B" & jumlahData + 3).Select 'input nis5
    ActiveCell.FormulaR1C1 = nis5
    Range("C" & jumlahData + 3).Select 'input nisn5
    ActiveCell.FormulaR1C1 = nisn5
    Range("D" & jumlahData + 3).Select 'input nama5
    ActiveCell.FormulaR1C1 = nama5
    Range("E" & jumlahData + 3).Select 'input sex5
    ActiveCell.FormulaR1C1 = sex5
    Range("F" & jumlahData + 3).Select 'input spirt5
    ActiveCell.FormulaR1C1 = spirt5
    Range("G" & jumlahData + 3).Select 'input sos5
    ActiveCell.FormulaR1C1 = sos5
    Range("H" & jumlahData + 3).Select 'input pnr5
    ActiveCell.FormulaR1C1 = pnr5
    Range("I" & jumlahData + 3).Select 'input pnk5
    ActiveCell.FormulaR1C1 = pnk5
    Range("J" & jumlahData + 3).Select 'input pp5
    ActiveCell.FormulaR1C1 = pp5
    Range("K" & jumlahData + 3).Select 'input knr5
    ActiveCell.FormulaR1C1 = knr5
    Range("L" & jumlahData + 3).Select 'input knk5
    ActiveCell.FormulaR1C1 = knk5
    Range("M" & jumlahData + 3).Select 'input kp5
    ActiveCell.FormulaR1C1 = kp5
   
    Sheets("Sheet11").Select
    jumlahData = Range("N1").Value
    Rows(jumlahData + 2 & ":" & jumlahData + 2).Select
    Selection.Copy
    Rows(jumlahData + 2 & ":" & jumlahData + 2).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A" & jumlahData + 3).Select 'input nomor6
    ActiveCell.FormulaR1C1 = jumlahData + 1
    Range("B" & jumlahData + 3).Select 'input nis6
    ActiveCell.FormulaR1C1 = nis6
    Range("C" & jumlahData + 3).Select 'input nisn6
    ActiveCell.FormulaR1C1 = nisn6
    Range("D" & jumlahData + 3).Select 'input nama6
    ActiveCell.FormulaR1C1 = nama6
    Range("E" & jumlahData + 3).Select 'input sex6
    ActiveCell.FormulaR1C1 = sex6
    Range("F" & jumlahData + 3).Select 'input spirt6
    ActiveCell.FormulaR1C1 = spirt6
    Range("G" & jumlahData + 3).Select 'input sos6
    ActiveCell.FormulaR1C1 = sos6
    Range("H" & jumlahData + 3).Select 'input pnr6
    ActiveCell.FormulaR1C1 = pnr6
    Range("I" & jumlahData + 3).Select 'input pnk6
    ActiveCell.FormulaR1C1 = pnk6
    Range("J" & jumlahData + 3).Select 'input pp6
    ActiveCell.FormulaR1C1 = pp6
    Range("K" & jumlahData + 3).Select 'input knr6
    ActiveCell.FormulaR1C1 = knr6
    Range("L" & jumlahData + 3).Select 'input knk6
    ActiveCell.FormulaR1C1 = knk6
    Range("M" & jumlahData + 3).Select 'input kp6
    ActiveCell.FormulaR1C1 = kp6
   
    Sheets("Sheet11").Select
    'pesan berhasil
    MsgBox "Input Data Berhasil !", vbInformation, "Terimakasih !"
   
End Sub

Contoh bisa anda gunakan Aplikasi Input Nilai Otomatis microsoft excel vba