Forum Discussion

ErebusMace's avatar
ErebusMace
Copper Contributor
Feb 24, 2025

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

I want to create a automated form to capture for example 

name

number submitted

date submitted 

then a box with a set of mandatory headers (say 10) that align with an input from another source to then copy to a response page via a submission button. 

 

I am aware on how to do all apart from a part of the form to get all the info across to the response page of say 100 rows and 10 columns. I can do it for single value just not a table.

 

If i set the headers and all entry's adhere to the column headers (100 x 10)how do I get the information of the multiple cells across rather than a single cell entry?

 

Thanks

excel
Macros and VBA

1 Reply

Sort By
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Mar 01, 2025

    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.

Resources