Transfering data from one sheet to a master sheet using Submit (command button)

Hi all,

I am getting Run time error: '1004' while running the code below which is to copy and update the data from the form on sheet 1 automatically to a sheet2. This is the attached aduit sheet that I am creating. I need to colate data from all the auditors who will audit on the same shared sheet and the data will come to sheet 2 automatically. Moreover, I was not able to insert command button activeX and got Cannot Insert Object error so I used the Form Button to assign below macro to it.

Sub Button1_Click()

Dim RecordNo As String, DateOfTransaction As Date

Worksheets("sheet1").Select
RecordNo = Range("D1")
DateOfTransaction = Range("D2")

Worksheets("sheet2").Select
Worksheets("sheet2").Range("A1").Select

If Worksheets("sheet2").Range("A1").Offset(1, 0) <> " " Then
Worksheets("sheet2").Range("A1").End(x1Down).Select
End If

ActiveCell.Offset(1, 0).Select
ActiveCell.Value = RecordNo
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = DateOfTransaction
Worksheets("sheet1").Select
Worksheets("sheet1").Range("D1").Select

End Sub


getting error on the line that is in bold.. Please help..