I have been asked how to save data entered into a form on
an Excel worksheet into another workbook and to clear the
form ready for re-use. Does anyone have any
recommendations on the best way to do this. I only have
Excel to work with. Many thanks for any help!

With both workbooks open, maybe this'll work:

Option Explicit
Sub testme01()

Dim toWks As Worksheet
Dim formWks As Worksheet
Dim NextRow As Long
Dim iCtr As Long
Dim beforeAddress As Variant
Dim afterCol As Variant

Set formWks = Workbooks("book1.xls").Worksheets("form")
Set toWks = Workbooks("book2.xls").Worksheets("HistoryLog")

beforeAddress = Array("C6", "C7", "C8", "d9", "f10", "g11", "h12", "j13")
afterCol = Array("c", "d", "e", "f", "g", "h", "i", "j")

If UBound(afterCol) < UBound(beforeAddress) Then
MsgBox "Error in before layout!"
Exit Sub
End If

With toWks
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1

With .Cells(NextRow, "A")
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With

.Cells(NextRow, "B").Value = Application.UserName

For iCtr = LBound(beforeAddress) To UBound(beforeAddress)
'move 'em in
.Cells(NextRow, afterCol(iCtr)).Value _
= formWks.Range(beforeAddress(iCtr)).Value
formWks.Range(beforeAddress(iCtr)).ClearContents
Next iCtr
End With

End Sub



Adjust the names of the workbooks & worksheets.

And fix the cells that get copied over and the columns that they get saved into.




Frances wrote:

I have been asked how to save data entered into a form on
an Excel worksheet into another workbook and to clear the
form ready for re-use. Does anyone have any
recommendations on the best way to do this. I only have
Excel to work with. Many thanks for any help!

--

Dave Peterson