Search code examples
excelvbacellcopy-pasteworksheet

Save entries from one 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 saves from Page1 to Page2 but only Date to Score are saved.

For the Yes/No/NA answers, I would like a one time save of these values from Page 1 to Page 2.

I get

Application-defined error

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

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.