excel - Copy rows to a new Sheet based on a value, times the value -

i've seen lot of answers questions seem not work properly. have dataset in sheet 1:

animal 1 | cat  | 5 | male             animal 2 | mouse| 3 | female     animal 3 | dog  | 1 | male 

and want on sheet 2 pressing command button on sheet 1:

animal 1 | cat  | 5 | male animal 1 | cat  | 5 | male animal 1 | cat  | 5 | male animal 1 | cat  | 5 | male animal 1 | cat  | 5 | male animal 2 | mouse| 3 | female animal 2 | mouse| 3 | female animal 2 | mouse| 3 | female animal 3 | dog  | 1 | male 

keep in mind sample, dataset 40 columns , on 1500 rows, value want copy in column c.

the work i've done far code this:

private sub commandbutton1_click()            dim currentrow long      dim currentnewsheetrow long: currentnewsheetrow = 1        currentrow = 1 1547 'the last row of data      dim timestoduplicate integer      timestoduplicate = cint(worksheets("sheet1").range("c" & currentrow).value)            dim integer      = 1 timestoduplicate          sheet2.range("a" & currentnewsheetrow).entirerow.value2 = sheet1.range("a" & currentrow).entirerow.value2          sheet2.range("b" & currentnewsheetrow).entirerow.value2 = sheet1.range("b" & currentrow).entirerow.value2          sheet2.range("c" & currentnewsheetrow).entirerow.value2 = sheet1.range("c" & currentrow).entirerow.value2          sheet2.range("d" & currentnewsheetrow).entirerow.value2 = sheet1.range("d" & currentrow).entirerow.value2          sheet2.range("e" & currentnewsheetrow).entirerow.value2 = sheet1.range("e" & currentrow).entirerow.value2          sheet2.range("f" & currentnewsheetrow).entirerow.value2 = sheet1.range("f" & currentrow).entirerow.value2          sheet2.range("g" & currentnewsheetrow).entirerow.value2 = sheet1.range("g" & currentrow).entirerow.value2          sheet2.range("h" & currentnewsheetrow).entirerow.value2 = sheet1.range("h" & currentrow).entirerow.value2          sheet2.range("i" & currentnewsheetrow).entirerow.value2 = sheet1.range("i" & currentrow).entirerow.value2          sheet2.range("j" & currentnewsheetrow).entirerow.value2 = sheet1.range("j" & currentrow).entirerow.value2          sheet2.range("k" & currentnewsheetrow).entirerow.value2 = sheet1.range("k" & currentrow).entirerow.value2          'continuous          currentnewsheetrow = currentnewsheetrow + 1      next  next currentrow    end sub

thank you!

try code:

option explicit  sub demo2()     dim lastrow long, lastcolumn long, rowindex long     dim srcsht worksheet, destsht worksheet     dim timestoduplicate long, long, j long, k long      application.screenupdating = false     application.calculation = xlcalculationmanual      set srcsht = thisworkbook.sheets("sheet5")  'sheet data     set destsht = thisworkbook.sheets("sheet6") 'output sheet      lastrow = srcsht.cells(rows.count, "a").end(xlup).row      'last row data     lastcolumn = cells(1, columns.count).end(xltoleft).column   'number of columns      rowindex = 1     = 1 lastrow                                'loop rows data         timestoduplicate = srcsht.cells(i, 3).value     'get number of times row displayed         j = 1 timestoduplicate                   'loop displaying row timestoduplicate no. of times             k = 1 lastcolumn                     'loop of columns                 destsht.cells(rowindex, k) = srcsht.cells(i, k) 'display data             next k             rowindex = rowindex + 1         next j     next      application.screenupdating = true     application.calculation = xlcalculationautomatic end sub