0

I have a form on a worksheet (Entry), when someone fills it in and click submit they store the values to another sheet(Tally) in the same workbook. The problem is I need it to collect all the submitted forms and place them in order on the Tally sheet, without overwriting previous values. At the moment I have:

r = Range("B5:E5")
Sheets("Tally").Range("B5:E5") = r

But this obviously overwrite the values on Tally each time the submit button is clicked. Something like:

r = Range("B5:E5")
x = next free row on Tally
Sheets("Tally").Range("Bx:Ex") = r
Community
  • 1
  • 1
pluke
  • 3,832
  • 5
  • 45
  • 68
  • 1
    This is a duplicate question. See my answer here for an answer to your question: http://stackoverflow.com/questions/11453658/find-the-last-cell-address-using-vba-excel/11453959#11453959 – mkingston Jan 21 '13 at 23:38
  • Use the offset property of the range object to get the row after the last one: e.g. lastCell.offset(1, 0) – mkingston Jan 21 '13 at 23:41
  • This has been asked many times, http://stackoverflow.com/questions/4872512/last-not-empty-cell-in-row-excel-vba/8583926#8583926 ..... http://stackoverflow.com/questions/14054906/excel-vba-endxldown-acting-weird etc – brettdj Jan 22 '13 at 00:18

1 Answers1

1
r = worksheets("entry").range("b5:e5")
nextFreeRow = worksheets("tally").Cells(Rows.Count,"B").end(xlup).row + 1
worksheets("tally").range("b" & nextFreeRow, "c" & nextFreeRow) = r

should work but i canno test this now. sry for bad formatting, writing from ipad

peter
  • 2,103
  • 7
  • 25
  • 51
  • 5
    This would work ok for xl2003, but it is not the bottom of the sheet for xl07/10/13. Use `Cells(Rows.Count,"B")` rather than `range("b65536")`. – brettdj Jan 22 '13 at 01:56