CODEWITHSUNDEEP
vbaexcel
pluke
pluke

Reputation: 4346

Return the location of next free row in an excel worksheet

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

Upvotes: 0

Views: 2105

Answers (1)

peter
peter

Reputation: 2103

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

Upvotes: 1

Related Questions