Automated form but with a copy paste area for data in a set format

Since you want a submission button to copy a table (100 rows × 10 columns) to a response page, VBA would be the best approach for the desktop version of Excel.

Here is an attempt to do it with VBA.

Please save your file beforehand. The VBA code is not tested.

Sub CopyDataToResponsePage()
    Dim wsSource As Worksheet
    Dim wsDestination As Worksheet
    Dim lastRow As Long
    Dim copyRange As Range
    Dim pasteRow As Long
    
    ' Define source and destination sheets
    Set wsSource = ThisWorkbook.Sheets("Form") ' Change to your form sheet name
    Set wsDestination = ThisWorkbook.Sheets("Responses") ' Change to your response sheet name
    
    ' Find the last used row in response sheet
    pasteRow = wsDestination.Cells(wsDestination.Rows.Count, 1).End(xlUp).Row + 1
    
    ' Define the range to copy (100 rows × 10 columns)
    Set copyRange = wsSource.Range("A5:J104") ' Adjust based on your table location
    
    ' Copy and paste the data to the response sheet
    copyRange.Copy
    wsDestination.Cells(pasteRow, 1).PasteSpecial Paste:=xlPasteValues
    
    ' Clear copied cells (optional)
    ' copyRange.ClearContents 
    
    ' Confirm submission
    MsgBox "Data successfully submitted!", vbInformation, "Success"
    
    ' Cleanup
    Application.CutCopyMode = False
End Sub

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.