Scan range of rows and export to 2nd worksheet

[Guest]

I created a spreadsheet as an entry form. There are 56 rows and 10 columns in
each row.

I want to create a command button that will scan each row and gather the
data, and then it will copy it into another workshet. After completion, it
will clear the first form, but it will not eliminate the formulas in the
fields.

Is this possible??

Thanks!!

 

[GaryDK]

Hi Adam,

This ought to get you started. Put this code into a module and assign
it to the command button on your data entry sheet.

Sub CopyRows()
Dim lentryRow As Long
Dim ldestRow As Long

' assumes data starts in column A for each row
' find last row with data in the active sheet
lentryRow = Cells(Rows.Count, 1).End(xlUp).Row
' assumes data starts in row 2
Range(Cells(2, 1), Cells(lentryRow, 10)).Copy

' find the first empty row on the destination sheet
ldestRow = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row +
1
' append new data
Worksheets("Sheet2").Cells(ldestRow, 1).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats
' assumes that last 5 cells in data entry form have formulas
' so just clear the actual entry cells
Range(Cells(2, 1), Cells(lentryRow, 5)).ClearContents
End Sub

I hope this helps,

Gary