excelvbasubmitspreadsheetdata-entry

Submit module to save entries from 1 sheet to another sheet

I have here 2 sheets.

Page1 sheet is where I put Date, Time, Ticket, Score and Yes/No/NA answers. I have a Submit button that calls a module that does the saving from Page1 to Page2 but only Date to Score are saved. I have a problem with Yes/No/NA answers. I would like to have a 1 time save of these values from Page 1 to Page 2 but I am getting Application-defined error. Please see below.

Page1 enter image description here

Page2 enter image description here

Module:

Option Explicit
Function saveTo()
    Dim sh, sh2 As Worksheet
    Set sh = ThisWorkbook.Sheets("Page2")
    Set sh2 = ThisWorkbook.Sheets("Page1")
    Dim n As Long
    n = sh.Range("A" & Application.Rows.Count).End(xlUp).Row
    
    sh.Range("A" & n + 1).Value = sh2.Range("E3")
    sh.Range("B" & n + 1).Value = sh2.Range("E4")
    sh.Range("C" & n + 1).Value = sh2.Range("E5")
    sh.Range("D" & n + 1).Value = sh2.Range("J3")
    sh.Range("$E:$BC" & n + 1).Value = sh2.Range("$E$7:$E$57")
End Function

Error

enter image description here

Is there a code to achieve a one-time save from Page1 to Page2 of the Yes/No/NA answers above? I appreciate your help.

Solution

  • Replace the errored line with this:

    sh.Range("$E" & n+1 & ":$BC" & n + 1).Value = worksheetfunction.Transpose(sh2.Range("$E$7:$E$57").Value)

    The range on the right side returns the value only when the range is one cell. Range with more than one cell requires the Value property.

    Transpose is necessary to adjust Row to Row referencing. Otherwise if set a range of one row to a range of one column, it will repeat the first value in the column through the whole row.