excel vba - Copy data to another selected sheet using Command Button -

sir, have problem in excell. have file in excell consist of several sheet. in sheet "student", there list name of students in range of cells (b2:b21) , list of subject in range (d2:d5).

in sheet "form", there form input result of test in range (d7:m7), cell name of student (d2) , cell subject (d4). (d2) , (d4) drop down list , source "student" sheet.

i created several subject sheets

i want copy data "form" sheet range (d7:m7) "math" sheet or "physic" sheet or etc range (c4:l23), depend on selected in cell (d2) , (d4). add activex control command button "submit". so, when klik "submit" button, data in "form" sheet range (d7:m7), copied sheet in range (c4:l23). need vba code program "submit" command button in order make proces done.

assuming "form" worksheet like

, "subject" worksheet following:

when button click , result copied corresponding subject worksheet:

following source code button:

option explicit dim resultworkbook workbook dim formworksheet worksheet dim subjectworksheet worksheet dim listrange range  sub copyresult()  dim lastrow long dim currentrow long  set resultworkbook = workbooks(activeworkbook.name) set formworksheet = resultworkbook.sheets("form") set subjectworksheet = resultworkbook.sheets(formworksheet.range("d4").value) set listrange = formworksheet.range("d7:m7")  application.screenupdating = false  lastrow = subjectworksheet.cells(rows.count, "b").end(xlup).row  currentrow = 4 lastrow     if subjectworksheet.range("b" & currentrow).value = formworksheet.range("d2").value         subjectworksheet.range("c" & currentrow & ":l" & currentrow).value = listrange.value     end if next  end sub